SQL Server 游标运用：查看所有数据库所有表大小信息（Sizes of All Tables in All Database）
一.本文所涉及的内容（Contents）[*]本文所涉及的内容（Contents）
[*]背景（Contexts）
[*]实现代码（SQL Codes）
[*]方法一：游标 + 系统存储过程sp_MSForEachDB
[*]方法二：封装sp_MSforeachtable + sys.databases
[*]方法三：系统存储过程sp_MSForEachDB + sp_MSforeachtable
[*]方法四：扩展sp_MSforeachdb + sp_MSforeachtable
[*]参考文献（References）
二.背景（Contexts）
之前写了篇关于：SQL Server 游标运用：查看一个数据库所有表大小信息（Sizes of All Tables in a Database）的文章，它罗列出某个数据所有表的信息，这些信息包括：表的记录数、数据记录占用空间、索引占用空间、没使用的空间等（如Figure1所示），现在我来讲述如何获取整个数据库实例中所有数据库所有表的信息（如Figure2所示）。
（Figure1：某数据库所有表信息）
（Figure2：所有数据库所有表信息）
三.实现代码（SQL Codes）
下面内容讲述了在实现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）的SQL脚本中进行改进，结合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 ''[''++'']'' FROM sys.tables WHERE TYPE=''U'';
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @tablespaceinfo EXEC sp_spaceused @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
SELECT * 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 .sys.objects WHERE object_id = OBJECT_ID(N'..') AND type in (N'U'))
BEGIN
CREATE TABLE ..(
(255) NULL,
NULL,
(20) NULL,
(20) NULL,
(20) NULL,
(20) NULL
) ON
END
ELSE
TRUNCATE TABLE tempdb.dbo.tablespaceinfo
EXEC sp_MSForEachDB 'USE [?];
--IF ''?'' not in(''tempdb'',''master'',''model'',''msdb'')
IF ''?'' in(''AdventureWorksLT2008R2'')
BEGIN
print ''?''
DECLARE @tablename VARCHAR(255);
DECLARE Info_cursor CURSOR FOR
SELECT ''[''++'']'' FROM ?.sys.tables WHERE TYPE=''U'';
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO tempdb.dbo.tablespaceinfo EXEC ?.dbo.sp_spaceused @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
END
'
--返回表
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:
-- Description:
-- Blog:
-- =============================================
--定义临时表
IF NOT EXISTS (SELECT * FROM .sys.objects WHERE object_id = OBJECT_ID(N'..') AND type in (N'U'))
BEGIN
CREATE TABLE ..(
NULL,
NULL,
NULL,
(100) NULL,
(100) NULL,
(100) NULL,
(100) NULL
) ON
END
ELSE
TRUNCATE TABLE tempdb.dbo.tablespaceinfo
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = COALESCE(@SQL,'') + '
USE [?];
--屏蔽掉系统数据库
IF ''?'' not in(''tempdb'',''master'',''model'',''msdb'')
--IF ''?'' like(''A%'')
BEGIN
PRINT ''?''
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.+'']'' 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_cursor
FETCH NEXT FROM Info_cursor INTO @schemas_name,@table_name
WHILE @@FETCH_STATUS = 0
BEGIN
--把表信息插入到临时表
SET @table_name = ''[''+@schemas_name+'']''+''.''+@table_name
INSERT INTO tempdb.dbo.tablespaceinfo(,,,,,)
EXEC ?.dbo.sp_spaceused @table_name
--更新数据库名称
UPDATE tempdb.dbo.tablespaceinfo SET = ''?'' WHERE IS NULL
FETCH NEXT FROM Info_cursor INTO @schemas_name,@table_name
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
END
'
--循环所有数据库
PRINT @SQL
EXEC sp_MSForEachDB @SQL
--返回临时表数据
SELECT * FROM tempdb.dbo.tablespaceinfo
ORDER BY ,Cast(Replace(reserved,'KB','') AS INT) DESC
--ORDER BY ,
DROP TABLE ..
(二) 还有没其他方式可以实现Figure2的效果呢？你可以考虑使用sp_MSforeachtable的方式实现，先使用存储过程sp_spaceused_db简单封装sp_MSforeachtable，它简单实现获取某个数据库的所有表，再使用拼凑生成批量的INSERT和UPDATE语句生成表信息数据，Script9与Script10需要分开执行，执行的效果如Figure2所示：
--Script4:
USE
GO
IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'.') AND type in (N'P', N'PC'))
DROP PROCEDURE .
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- Blog:
-- =============================================
CREATE PROCEDURE .
@db_name nvarchar(776) = null
AS
BEGIN
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:
-- Description:
-- Blog:
-- =============================================
CREATE TABLE ..(
NULL,
NULL,
NULL,
(100) NULL,
(100) NULL,
(100) NULL,
(100) NULL
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + '
INSERT INTO ..(,,,,,)
EXEC .dbo.sp_spaceused_db ' + QUOTENAME(name,'''') + '
UPDATE .. SET = ' + QUOTENAME(name,'''') + '
WHERE IS NULL'
FROM sys.databases WHERE database_id >4
PRINT(@SQL)
EXECUTE(@SQL)
SELECT * FROM ..
ORDER BY ,Cast(Replace(reserved,'KB','') AS INT) DESC
DROP TABLE ..
(三) 如果你想使用sp_MSForEachDB与sp_MSforeachtable（sp_MSforeach_worker、sp_MStablespace）结合的方式实现Figure2效果，刚开始测试的时候发现这两个存储过程在解释“?”的时候会出现歧义，SQL无法理解它是指数据库还是表，难道微软会做那么愚蠢的事情？后来查看了这两个存储过程的SQL脚本，发现是有办法解决上面问题的。
--Script6:
-- =============================================
-- Author:
-- Create date:
-- Description:
-- Blog:
-- =============================================
IF NOT EXISTS (SELECT * FROM .sys.objects WHERE object_id = OBJECT_ID(N'..') AND type in (N'U'))
BEGIN
CREATE TABLE ..(
NULL,
NULL,
NULL,
(100) NULL,
(100) NULL,
(100) NULL,
(100) NULL
) ON
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(,,,,,)
EXEC [?].sys.sp_MSforeachtable @command1="sp_spaceused N''$''",@replacechar=N''$''
--更新数据库名称
UPDATE tempdb.dbo.tablespaceinfo SET = ''?'' WHERE IS NULL
END'
PRINT (@SQL)
--所有数据库
EXEC sp_MSforeachdb @command1="print '?'",@command2=@SQL, @replacechar=N'?'
--返回临时表数据
SELECT * FROM tempdb.dbo.tablespaceinfo
ORDER BY ,Cast(Replace(reserved,'KB','') AS INT) DESC
DROP TABLE ..
(四) 上面的Script6脚本过滤数据的方式比较麻烦，所以我对sp_MSforeachdb系统存储过程进行了一些修改，生成一个新的存储过程sp_MSforeachdb_Filter，详情请查看：SQL Server 游标运用：查看一个数据库所有表大小信息（Sizes of All Tables in a Database），在创建了存储过程sp_MSforeachdb_Filter的情况下执行下面的SQL脚本，你可以随意修改@whereand参数来满足你的过滤条件，非常方便。
--Script7:
-- =============================================
-- Author:
-- Create date:
-- Description:
-- Blog:
-- =============================================
IF NOT EXISTS (SELECT * FROM .sys.objects WHERE object_id = OBJECT_ID(N'..') AND type in (N'U'))
BEGIN
CREATE TABLE ..(
NULL,
NULL,
NULL,
(100) NULL,
(100) NULL,
(100) NULL,
(100) NULL
) ON
END
ELSE
TRUNCATE TABLE tempdb.dbo.tablespaceinfo
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + '
--插入表信息
INSERT INTO tempdb.dbo.tablespaceinfo(,,,,,)
EXEC [?].sys.sp_MSforeachtable @command1="sp_spaceused N''$''",@replacechar=N''$''
--更新数据库名称
UPDATE tempdb.dbo.tablespaceinfo SET = ''?'' WHERE IS NULL'
PRINT (@SQL)
----过滤数据库
--EXEC @command1="print '?'",@command2=@SQL, @replacechar=N'?',
--@whereand=" and not in('tempdb','master','model','msdb') "
--过滤数据库
EXEC @command1="print '?'",@command2=@SQL, @replacechar=N'?',
@whereand=" and > 4 "
--返回临时表数据
SELECT * FROM tempdb.dbo.tablespaceinfo
ORDER BY ,Cast(Replace(reserved,'KB','') AS INT) DESC
DROP TABLE ..
四.参考文献（References）
与存储过程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）
页:
[1]