好得很程序员自学网

<tfoot draggable='sEl'></tfoot>

Sizes of All Tables in All Database

Sizes of All Tables in All Database

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） 的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  ]

三、 参考文献

与存储过程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）

 

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

作者： 听风吹雨

出处： http://gaizai.cnblogs.com/

版权：本文版权归作者和博客园共有

转载：欢迎转载，必须保留原文链接

邮箱： gaizai@126.com

格言：不喜欢是因为你不会 && 因为会所以喜欢

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

 

 

 

分类:  00.SQL Server

标签:  SQL Server ,  数据库

作者： Leo_wl

　　　　

出处： http://www.cnblogs.com/Leo_wl/

　　　　

本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。

版权信息

查看更多关于Sizes of All Tables in All Database的详细内容...

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did45806

  阅读：38次

上一篇： Cygwin安装

下一篇：初探CoffeeScript1

相关资讯