IF NOT EXISTS (SELECT * FROM [tempdb].sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[tablespaceinfo]') AND type in (N'U'))
BEGIN
CREATE TABLE [tempdb].[dbo].[tablespaceinfo](
[db_name] [sysname] NULL,
[table_name] [sysname] NULL,
[rows] [bigint] NULL,
[reserved] [varchar](100) NULL,
[data] [varchar](100) NULL,
[index_size] [varchar](100) NULL,
[unused] [varchar](100) NULL
) ON [PRIMARY]
END
ELSE
TRUNCATE TABLE tempdb.dbo.tablespaceinfo
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + '
USE [?];
--屏蔽掉系统数据库
IF ''?'' not in(''tempdb'',''master'',''model'',''msdb'')
BEGIN
--插入表信息
INSERT INTO tempdb.dbo.tablespaceinfo([table_name],[rows],[reserved],[data],[index_size],[unused])
EXEC [?].sys.sp_MSforeachtable @command1="sp_spaceused N''$''",@replacechar=N''$''
--更新数据库名称
UPDATE tempdb.dbo.tablespaceinfo SET [db_name] = ''?'' WHERE [db_name] IS NULL
END'
PRINT (@SQL)
--所有数据库
EXEC sp_MSforeachdb @command1="print '?'",@command2=@SQL, @replacechar=N'?'
--返回临时表数据
SELECT * FROM tempdb.dbo.tablespaceinfo
ORDER BY [db_name],Cast(Replace(reserved,'KB','') AS INT) DESC
DROP TABLE [tempdb].[dbo].[tablespaceinfo]