SQL Server 查看所有数据库所有表大小信息（Sizes of All Tables in All Database）【复杂版本】

一、背景

之前写了篇关于：SQL Server 游标运用：查看一个数据库所有表大小信息（Sizes of All Tables in a Database）的文章，它罗列出某个数据所有表的信息，这些信息包括：表的记录数、数据记录占用空间、索引占用空间、没使用的空间等（如Figure1所示），现在我来讲述如何获取整个数据库实例中所有数据库所有表的信息（如Figure2所示）。

（Figure1：某数据库所有表信息）

（Figure2：所有数据库所有表信息）

二、实现方法

下面内容讲述了在实现Figure2过程中遇到的一些问题，如果你对这些问题不感兴趣可以直接看最后实现的SQL脚本。下面讲述了4种实现方法：

1. 游标 + 系统存储过程sp_MSForEachDB，实现脚本为Script3；

2. 封装sp_MSforeachtable + sys.databases，实现脚本为Script4和Script5；

3. 系统存储过程sp_MSForEachDB + sp_MSforeachtable，实现脚本为Script6；

4. 扩展sp_MSforeachdb + sp_MSforeachtable，实现脚本为Script7；

(一) 我们在Sql Server游标运用：查看一个数据库所有表大小信息（Sizes of All Tables in a Database）QL脚本中进行改进，结合sp_MSForEachDB系统存储过程进行实现：

1) 既然有了获取某个数据库所有表信息的脚本，那就可以在外层再套使用sp_MSForEachDB系统存储过程，下面的Script1脚本可以获取到所有数据库的所有表的信息，效果如Figure3所示：

--Script1:--查看所有数据库所有表信息EXEC sp_MSForEachDB 'USE [?];DECLARE @tablespaceinfo TABLE (    nameinfo VARCHAR(50),    rowsinfo INT,    reserved VARCHAR(20),    datainfo VARCHAR(20),    index_size VARCHAR(20),    unused VARCHAR(20))DECLARE @tablename VARCHAR(255);DECLARE Info_cursor CURSOR FOR    SELECT ''[''+[name]+'']'' FROM sys.tables WHERE TYPE=''U'';OPEN Info_cursorFETCH NEXT FROM Info_cursor INTO @tablenameWHILE @@FETCH_STATUS = 0BEGIN    INSERT INTO @tablespaceinfo EXEC sp_spaceused @tablename    FETCH NEXT FROM Info_cursor    INTO @tablenameENDCLOSE Info_cursorDEALLOCATE Info_cursorSELECT * FROM @tablespaceinfo    ORDER BY Cast(Replace(reserved,''KB'','''') AS INT) DESC'

（Figure3：所有数据库所有表）

2) 上图Figure3有两个缺点，第一是返回的数据太分散，没有统一表进行管理，第二是需要过滤master、model、msdb和tempdb等系统数据库，因为我们完全不关心系统数据库，下面的SQL脚本展示在使用sp_msforeachdb的时候如何排除某个数据库，效果如Figure4所示：

--sp_msforeachdb排除某个数据库EXEC sp_msforeachdb 'IF ''?'' <> ''tempdb'' print ''?'''

（Figure4：sp_msforeachdb排除某个数据库）

3) 下面的SQL脚本展示在使用sp_msforeachdb的时候如何排除多个数据库，效果如Figure5所示：

--sp_msforeachdb排除多个数据库EXEC sp_msforeachdb 'IF ''?'' not in(''tempdb'',''master'',''model'',''msdb'') print ''?'''

（Figure5：sp_msforeachdb排除多个数据库）

4) 把上面的SQL脚本运用到之前获取某个数据库表信息的SQL脚本中，但是执行的过程中出现了Figure6的错误信息：

--Script2:--查看所有数据库所有表信息IF NOT EXISTS (SELECT * FROM [tempdb].sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[tablespaceinfo]') AND type in (N'U'))BEGINCREATE TABLE [tempdb].[dbo].[tablespaceinfo](    [nameinfo] [varchar](255) NULL,    [rowsinfo] [int] NULL,    [reserved] [varchar](20) NULL,    [datainfo] [varchar](20) NULL,    [index_size] [varchar](20) NULL,    [unused] [varchar](20) NULL) ON [PRIMARY]ENDELSE    TRUNCATE TABLE tempdb.dbo.tablespaceinfoEXEC sp_MSForEachDB 'USE [?];--IF ''?'' not in(''tempdb'',''master'',''model'',''msdb'') IF ''?'' in(''AdventureWorksLT2008R2'') BEGINprint ''?''DECLARE @tablename VARCHAR(255);DECLARE Info_cursor CURSOR FOR    SELECT ''[''+[name]+'']'' FROM ?.sys.tables WHERE TYPE=''U'';OPEN Info_cursorFETCH NEXT FROM Info_cursor INTO @tablenameWHILE @@FETCH_STATUS = 0BEGIN    INSERT INTO tempdb.dbo.tablespaceinfo EXEC ?.dbo.sp_spaceused @tablename    FETCH NEXT FROM Info_cursor    INTO @tablenameENDCLOSE Info_cursorDEALLOCATE Info_cursorEND'--返回表SELECT * FROM tempdb.dbo.tablespaceinfo    --ORDER BY Cast(Replace(reserved,'KB','') AS INT) DESC    ORDER BY nameinfo

（Figure6：错误信息）

5) 经过一番查找，最后发现是因为AdventureWorksLT2008R2数据库的安全中的架构是SalesLT，不是默认的dbo，所以报了Figure6的错误信息，但是如果使用sp_MSforeachtable，那就不用理会框架的问题。

（Figure7：SalesLT架构名）

只要我们在表名称前面加入正确的架构名，那就可以正确执行了，如Figure8所示：

--使用正确的架构名AdventureWorksLT2008R2.dbo.sp_spaceused 'SalesLT.Address'

（Figure8：正确的架构名）

6) 经过上面经验的总结，关于所有数据库所有表的信息的SQL脚本就水到渠成了，下面就是全部的SQL脚本，注意过滤的方式可以写成：IF ''?'' like(''A%'') ，执行的效果如Figure2所示：

--Script3:-- =============================================-- Author:        <听风吹雨>-- Create date:    <2013.05.03>-- Description:    <查看所有数据库所有表信息>-- Blog:        <http://www.cnblogs.com/gaizai/>-- =============================================--定义临时表IF NOT EXISTS (SELECT * FROM [tempdb].sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[tablespaceinfo]') AND type in (N'U'))BEGINCREATE 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]ENDELSE    TRUNCATE TABLE tempdb.dbo.tablespaceinfoDECLARE @SQL NVARCHAR(MAX)SET @SQL = COALESCE(@SQL,'') + 'USE [?];--屏蔽掉系统数据库IF ''?'' not in(''tempdb'',''master'',''model'',''msdb'')--IF ''?'' like(''A%'')BEGINPRINT ''?''DECLARE @schemas_name VARCHAR(255);DECLARE @table_name VARCHAR(255);DECLARE Info_cursor CURSOR FOR    --获取schemas_name和table_name    SELECT b.name AS schemas_name,''[''+a.[name]+'']'' AS table_name FROM ?.sys.tables AS a        LEFT JOIN ?.sys.schemas AS b        ON a.schema_id = b.schema_id        WHERE TYPE=''U''OPEN Info_cursorFETCH NEXT FROM Info_cursor INTO @schemas_name,@table_nameWHILE @@FETCH_STATUS = 0BEGIN    --把表信息插入到临时表    SET @table_name = ''[''+@schemas_name+'']''+''.''+@table_name    INSERT INTO tempdb.dbo.tablespaceinfo([table_name],[rows],[reserved],[data],[index_size],[unused])        EXEC ?.dbo.sp_spaceused @table_name    --更新数据库名称    UPDATE tempdb.dbo.tablespaceinfo SET [db_name] = ''?'' WHERE [db_name] IS NULL        FETCH NEXT FROM Info_cursor INTO @schemas_name,@table_nameENDCLOSE Info_cursorDEALLOCATE Info_cursorEND'--循环所有数据库PRINT @SQLEXEC sp_MSForEachDB @SQL--返回临时表数据SELECT * FROM tempdb.dbo.tablespaceinfo    ORDER BY [db_name],Cast(Replace(reserved,'KB','') AS INT) DESC--ORDER BY [db_name],[table_name]DROP TABLE [tempdb].[dbo].[tablespaceinfo]

(二) 还有没其他方式可以实现Figure2的效果呢？你可以考虑使用sp_MSforeachtable的方式实现，先使用存储过程sp_spaceused_db简单封装sp_MSforeachtable，它简单实现获取某个数据库的所有表，再使用拼凑生成批量的INSERT和UPDATE语句生成表信息数据，Script9与Script10需要分开执行，执行的效果如Figure2所示：

--Script4:USE [tempdb]GOIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_spaceused_db]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sp_spaceused_db]GO-- =============================================-- Author:        <听风吹雨>-- Create date: <2013.05.06>-- Description:    <封装sp_MSforeachtable>-- Blog:        <http://www.cnblogs.com/gaizai/>-- =============================================CREATE PROCEDURE [dbo].[sp_spaceused_db]    @db_name nvarchar(776) = nullASBEGIN    DECLARE @SQL NVARCHAR(MAX)    SELECT @SQL = COALESCE(@SQL,'') + '        EXEC ['+@db_name+'].dbo.sp_MSforeachtable @command1="sp_spaceused ''?''"'        PRINT(@SQL)    EXECUTE(@SQL)END

--Script5:-- =============================================-- Author:        <听风吹雨>-- Create date: <2013.05.06>-- Description:    <查看所有数据库所有表信息>-- Blog:        <http://www.cnblogs.com/gaizai/>-- =============================================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)DECLARE @SQL NVARCHAR(MAX)SELECT @SQL = COALESCE(@SQL,'') + 'INSERT INTO [tempdb].[dbo].[tablespaceinfo]([table_name],[rows],[reserved],[data],[index_size],[unused])    EXEC [tempdb].dbo.sp_spaceused_db ' + QUOTENAME(name,'''') + 'UPDATE [tempdb].[dbo].[tablespaceinfo] SET [db_name] = ' + QUOTENAME(name,'''') + '    WHERE [db_name] IS NULL'FROM sys.databases WHERE database_id >4PRINT(@SQL)EXECUTE(@SQL)SELECT * FROM [tempdb].[dbo].[tablespaceinfo]    ORDER BY [db_name],Cast(Replace(reserved,'KB','') AS INT) DESCDROP TABLE [tempdb].[dbo].[tablespaceinfo]

(三) 如果你想使用sp_MSForEachDB与sp_MSforeachtable（sp_MSforeach_worker、sp_MStablespace）结合的方式实现Figure2效果，刚开始测试的时候发现这两个存储过程在解释“?”的时候会出现歧义，SQL无法理解它是指数据库还是表，难道微软会做那么愚蠢的事情？后来查看了这两个存储过程的SQL脚本，发现是有办法解决上面问题的。

--Script6:-- =============================================-- Author:        <听风吹雨>-- Create date: <2013.05.08>-- Description:    <查看所有数据库所有表信息>-- Blog:        <http://www.cnblogs.com/gaizai/>-- =============================================IF NOT EXISTS (SELECT * FROM [tempdb].sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[tablespaceinfo]') AND type in (N'U'))BEGINCREATE 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]ENDELSE    TRUNCATE TABLE tempdb.dbo.tablespaceinfoDECLARE @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 NULLEND'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) DESCDROP TABLE [tempdb].[dbo].[tablespaceinfo]

(四) 上面的Script6脚本过滤数据的方式比较麻烦，所以我对sp_MSforeachdb系统存储过程进行了一些修改，生成一个新的存储过程sp_MSforeachdb_Filter，详情请查看：SQL Server 游标运用：查看一个数据库所有表大小信息（Sizes of All Tables in a Database），在创建了存储过程sp_MSforeachdb_Filter的情况下执行下面的SQL脚本，你可以随意修改@whereand参数来满足你的过滤条件，非常方便。

--Script7:-- =============================================-- Author:        <听风吹雨>-- Create date: <2013.05.08>-- Description:    <查看所有数据库所有表信息>-- Blog:        <http://www.cnblogs.com/gaizai/>-- =============================================IF NOT EXISTS (SELECT * FROM [tempdb].sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[tablespaceinfo]') AND type in (N'U'))BEGINCREATE 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]ENDELSE    TRUNCATE TABLE tempdb.dbo.tablespaceinfoDECLARE @SQL NVARCHAR(MAX)SELECT @SQL = COALESCE(@SQL,'') + '    --插入表信息    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'PRINT (@SQL)----过滤数据库--EXEC [sp_MSforeachdb_Filter] @command1="print '?'",@command2=@SQL, @replacechar=N'?',--@whereand=" and [name] not in('tempdb','master','model','msdb') "--过滤数据库EXEC [sp_MSforeachdb_Filter] @command1="print '?'",@command2=@SQL, @replacechar=N'?',@whereand=" and [dbid] > 4 "--返回临时表数据SELECT * FROM tempdb.dbo.tablespaceinfo    ORDER BY [db_name],Cast(Replace(reserved,'KB','') AS INT) DESCDROP TABLE [tempdb].[dbo].[tablespaceinfo]

三、参考文献

与存储过程sp_MSforeachdb类似的存储过程sp_MSforeachdb

SQL Server数据库开发顶级技巧

sp_MSforeachtable使用方法

How to get information about all databases without a loop

关于quotename的用法

SQL Server 游标运用：查看一个数据库所有表大小信息（Sizes of All Tables in a Database）

-------------------华丽分割线-------------------

作者：听风吹雨