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

erlchina

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

• 本文所涉及的内容（Contents）
  
• 背景（Contexts）
  
• 实现代码（SQL Codes）
  
  
  • 方法一：运用游标
    
  • 方法二：运用系统存储过程
    
  • 方法三：拼接SQL
    
  
  
• 参考文献（References）
  

二.背景（Contexts）
　　在性能调优或者需要了解某数据库表信息的时候，最直观的方式就是罗列出这个数据所有表的信息，这些信息包括：表的记录数、数据记录占用空间、索引占用空间、未使用的空间等（如Figure1所示），有了这些信息你可以简单的判断这个数据库来自数据上的压力可能是某个表造成的。因为表数据越大，对数据库性能的影响越大。
　　要实现某个数据库所有表的信息，可以通过游标的形式获取相应的数据，下图Figure1返回某数据库中所有表的信息：

　　（Figure1：某数据库所有表信息）
　　也许你并不满足于Figure1的信息，你希望获取整个数据库实例中所有数据库所有表的信息（如Figure2所示），如果想了解里面的实现可以参考：SQL Server 查看所有数据库所有表大小信息（Sizes of All Tables in All Database）

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

三.实现代码（SQL Codes）
　　为了实现Figure1的效果，有三种方式可以实现：
　　1. 运用游标循环每个表，调用系统存储过程sp_spaceused把结果保存到临时表，可以过滤表；
　　2. 运用系统存储过程sp_MSforeachtable把结果保存到临时表，可以过滤表；
　　3. 运用系统表INFORMATION_SCHEMA.TABLES与系统存储过程sp_spaceused结合起来，拼接出每个表的INSERT语句，把结果保存到临时表，可以过滤表；
　　

　　(一) 运用游标循环每个表，调用系统存储过程sp_spaceused把结果保存到临时表，可以过滤表；

　　首先定义一个临时表变量@tablespaceinfo用于保存表的信息，使用游标读取sys.tables中的表名称，再通过sp_spaceused获取这个表的相关数据插入到临时表变量@tablespaceinfo。下面是SQL脚本的实现，效果就如Figure1所示：



--Script1:
--查看某数据库所有表的信息
DECLARE @tablespaceinfo TABLE (
[name] SYSNAME,
[rows] BIGINT,
[reserved] VARCHAR(100),
[data] VARCHAR(100),
[index_size] VARCHAR(100),
[unused] VARCHAR(100)
)
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

　　(二) 运用系统存储过程sp_MSforeachtable把结果保存到临时表，可以过滤表；；

　　下面Script2脚本是通过使用系统存储过程sp_MSforeachtable，返回的结果如Figure1所示：



--Script2:
--查看某数据库所有表的信息
DECLARE @tablespaceinfo TABLE (
[name] SYSNAME,
[rows] BIGINT,
[reserved] VARCHAR(100),
[data] VARCHAR(100),
[index_size] VARCHAR(100),
[unused] VARCHAR(100)
)
INSERT INTO @tablespaceinfo EXEC sp_MSforeachtable @command1="sp_spaceused '?'"
SELECT * FROM @tablespaceinfo
ORDER BY Cast(Replace(reserved,'KB','') AS INT) DESC
　　1) 如果你想使用sp_MSforeachtable但是又想过滤掉一些表，你可以在查询@tablespaceinfo的时候加入Where条件（这种方式就不演示了）；或者直接在开始使用sp_MSforeachtable的时候就进行过滤：



--获取所有表信息
EXEC sp_MSforeachtable @command1="sp_spaceused '?'"
　　如果我们加入@whereand参数进行过滤，但是会出现下图的错误信息：



--过滤某些表
EXEC sp_MSforeachtable @whereand="and [name] like 't%'", @command1="sp_spaceused '?'"

　　（Figure3：错误信息）
　　经过查看sp_MSforeachtable的源代码，发现是有多个[name]字段的，所以指明字段，上面的错误就可以解决了，效果如下图所示。把下面的SQL代码替换Script2的部分代码就可以了。



--过滤某些表
EXEC sp_MSforeachtable @whereand="and o.[name] like 't%'", @command1="sp_spaceused '?'"
--或者
EXEC sp_MSforeachtable @whereand="and syso.[name] like 't%'", @command1="sp_spaceused '?'"

　　（Figure4：过滤返回的结果）
　　2) 在sp_MSforeachtable系统存储过程，有@whereand的参数可以对表进行过滤，sp_MSForEachDB系统存储是否也有同样的功能呢？查看了sp_MSForEachDB的源码，我们发现是没有这个功能的，为实现这个功能，我对sp_MSForEachDB进行修改，重新命名为：sp_MSForEachDB_Filter



--扩展sp_MSforeachdb
create proc dbo.[sp_MSforeachdb_Filter]
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
@whereand nvarchar(2000) = null,@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
set deadlock_priority low
declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
select @dbinaccessible = N'0x80000000'        
if (@precommand is not null)
exec(@precommand)
declare @origdb nvarchar(128)
select @origdb = db_name()
exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
N' where (d.status & ' + @inaccessible + N' = 0)' +
N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' + @whereand)
declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1
if (@retval = 0 and @postcommand is not null)
exec(@postcommand)
declare @tempdb nvarchar(258)
SELECT @tempdb = REPLACE(@origdb, N']', N']]')
exec (N'use ' + N'[' + @tempdb + N']')
return @retval
　　上面的存储过程sp_MSforeachdb_Filter是依照sp_MSforeachtable进行简单修改的，创建之后就可以使用下面的SQL脚本进行过滤数据库了，效果如下图所示：



--过滤数据库
EXEC [sp_MSforeachdb_Filter] @command1="print '?'",
@whereand=" and [name] not in('tempdb','master','model','msdb') "

　　（Figure5：过滤数据库）

　　(三) 运用系统表INFORMATION_SCHEMA.TABLES与系统存储过程sp_spaceused结合起来，拼接出每个表的INSERT语句，把结果保存到临时表，可以过滤表；

　　一次偶然的机会看到一个同样能实现Figure1效果的SQL脚本，它使用了系统表INFORMATION_SCHEMA.TABLES，下面是我修改过的SQL脚本，区别就在于可以满足对不同架构表的查询。原文详情可以参考：How to get information about all databases without a loop



--Script3:Sizes of All Tables in a Database
--exec sp_MSforeachtable 'print ''?'' exec sp_spaceused ''?'''
--在它的基础上做了些修改，适合不同的框架dbo等
IF OBJECT_ID('tempdb..#TablesSizes') IS NOT NULL
DROP TABLE #TablesSizes
CREATE TABLE #TablesSizes (TableName sysname, Rows bigint, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))
DECLARE @sql VARCHAR(MAX)
SELECT @sql = COALESCE(@sql,'') + '
INSERT INTO #TablesSizes execute sp_spaceused ''' + QUOTENAME(TABLE_SCHEMA,'[]') + '.' + QUOTENAME(Table_Name,'[]') + ''''
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
PRINT (@SQL)
EXECUTE (@SQL)
SELECT * FROM #TablesSizes ORDER BY TableName
　　上面PRINT出来的脚本类似下图Figure6所示：

（Figure6：拼接的SQL代码）

四.参考文献（References）
　　与存储过程sp_MSforeachdb类似的存储过程sp_MSforeachdb
　　SQL Server数据库开发顶级技巧
　　sp_MSforeachtable使用方法
　　How to get information about all databases without a loop
　　关于quotename的用法