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]