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

一.本文所涉及的内容（Contents）

本文所涉及的内容（Contents）
背景（Contexts）
实现代码（SQL Codes）
参考文献（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 ''[''+[name]+'']'' 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 [tempdb].sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[tablespaceinfo]') AND type in (N'U'))
BEGIN
CREATE 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]
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 ''[''+[name]+'']'' 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:    <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'))
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)
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.[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_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([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_name
END

CLOSE Info_cursor
DEALLOCATE Info_cursor
END
'
--循环所有数据库
PRINT @SQL
EXEC 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]
GO

IF  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) = 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: <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 >4

PRINT(@SQL)
EXECUTE(@SQL)

SELECT * FROM [tempdb].[dbo].[tablespaceinfo]
    ORDER BY [db_name],Cast(Replace(reserved,'KB','') AS INT) DESC

DROP 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'))
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]

(四) 上面的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'))
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,'') + '
    --插入表信息
    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) DESC

DROP TABLE [tempdb].[dbo].[tablespaceinfo]

四.参考文献（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）

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

一.本文所涉及的内容（Contents）

二.背景（Contexts）

三.实现代码（SQL Codes）

四.参考文献（References）

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

一.本文所涉及的内容（Contents）

二.背景（Contexts）

三.实现代码（SQL Codes）

四.参考文献（References）

热门文章

最新文章

相关课程

相关电子书

相关实验场景