·天新网首页·加入收藏·设为首页·网站导航
数码笔记本手机摄像机相机MP3MP4GPS
硬件台式机网络服务器主板CPU硬盘显卡
办公投影打印传真
家电电视影院空调
游戏网游单机动漫
汽车新车购车试驾
下载驱动源码
学院开发设计
考试公务员高考考研
业界互联网通信探索
SQL Server 查看所有数据库所有表大小信息(Sizes of All Tables in All Database)
http://www.21tx.com 2013年09月11日

1 2 3 下一页

一、背景

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

SQL Server 查看所有数据库所有表大小信息(Sizes of All Tables in All Database)

(Figure1:某数据库所有表信息)

SQL Server 查看所有数据库所有表大小信息(Sizes of All Tables in All Database)

(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'

SQL Server 查看所有数据库所有表大小信息(Sizes of All Tables in All Database)

(Figure3:所有数据库所有表)

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

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

SQL Server 查看所有数据库所有表大小信息(Sizes of All Tables in All Database)

(Figure4:sp_msforeachdb排除某个数据库)

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

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

SQL Server 查看所有数据库所有表大小信息(Sizes of All Tables in All Database)

(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

SQL Server 查看所有数据库所有表大小信息(Sizes of All Tables in All Database)

(Figure6:错误信息)

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

上一篇: SQL Server 解读【已分区索引的特殊指导原则】(3) - 非聚集索引分区
下一篇: SQL Server 解读【已分区索引的特殊指导原则】(2)- 唯一索引分区

1 2 3 下一页

关于我们 | 联系我们 | 加入我们 | 广告服务 | 投诉意见 | 网站导航
Copyright © 2000-2011 21tx.com, All Rights Reserved.
晨新科技 版权所有 Created by TXSite.net