计算数据库中各个表的数据量和每行记录所占用空间--添加架构信息-读后感及知识整理

参考文章:

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

分享一个SQLSERVER脚本(计算数据库中各个表的数据量和每行记录所占用空间)

监控SQLServer 数据库表每天的空间变化情况

仔细拜读上面三位的文章,不会的知识点又参考了MSDN,巩固了知识点如下:

知识点:

1。表的架构信息,涉及的系统对象 sys.schemas 和 INFORMATION_SCHEMA.TABLES,但后者不是官方推荐方式,具体参考msdn

2。表的基本信息,涉及的系统对象 sys.tables

3。临时表的创建与删除,OBJECT_ID 的用法

4。游标的使用及各个选项的意义,CURSOR 本地, 前向,只读,静态

5。数据类型的转换,整数除法

 

重新整理脚如下,已支持不同架构的表

/*
计算数据库中各个表的数据量和每行记录所占用空间

使用前需要用 USE 指定数据库
*/

SET NOCOUNT ON;

IF OBJECT_ID(‘tempdb..#tablespaceinfo‘) IS NOT NULL
	DROP TABLE #tablespaceinfo;

CREATE TABLE #tablespaceinfo(nameinfo VARCHAR(500)
      ,rowsinfo BIGINT
      ,reserved VARCHAR(200)
      ,datainfo VARCHAR(200)
      ,index_size VARCHAR(20)
      ,unused VARCHAR(20));

IF OBJECT_ID(‘tempdb..#tablespaceinfo_temp‘) IS NOT NULL
	DROP TABLE #tablespaceinfo_temp;

CREATE TABLE #tablespaceinfo_temp(nameinfo VARCHAR(500)
      ,rowsinfo BIGINT
      ,reserved VARCHAR(200)
      ,datainfo VARCHAR(200)
      ,index_size VARCHAR(20)
      ,unused VARCHAR(20));
 
DECLARE @tablename VARCHAR(255);  

--INFORMATION_SCHEMA.TABLES 不是系统推荐的获取架构的方式,所以改用下面的语句
DECLARE Info_cursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
    SELECT QUOTENAME(S.name,‘[]‘)+‘.‘+QUOTENAME(T.name,‘[]‘) FROM sys.tables AS T LEFT JOIN sys.schemas AS S ON T.schema_id=S.schema_id WHERE T.type = ‘U‘;  

OPEN Info_cursor;
FETCH NEXT FROM Info_cursor INTO @tablename;
 
WHILE @@FETCH_STATUS = 0
    BEGIN
		DELETE FROM #tablespaceinfo_temp;
		--sp_spaceused 在 sql 2005前不可用
        INSERT INTO #tablespaceinfo_temp(nameinfo,rowsinfo,reserved,datainfo,index_size,unused) EXEC sp_spaceused @tablename;
		INSERT INTO #tablespaceinfo(nameinfo,rowsinfo,reserved,datainfo,index_size,unused) 
			SELECT @tablename,rowsinfo,reserved,datainfo,index_size,unused FROM #tablespaceinfo_temp;
        FETCH NEXT FROM Info_cursor INTO @tablename;
    END

CLOSE Info_cursor;
DEALLOCATE Info_cursor;
DROP TABLE #tablespaceinfo_temp;

--汇总记录
SELECT *,(CASE rowsinfo
			WHEN 0 THEN 0
			ELSE CONVERT(DECIMAL(19,8),CONVERT(DECIMAL(19,2),LEFT(datainfo,LEN(datainfo)-3)) /rowsinfo)
			END ) AS ‘每行记录大概占用空间(KB)‘
FROM #tablespaceinfo
--结果排序在此处修改
ORDER BY CAST(REPLACE(reserved, ‘KB‘, ‘‘) AS INT) DESC;

DROP TABLE [#tablespaceinfo];

 

上面三篇文章,详细说明了怎样计算一个数据库中各个表的行数,所占空间等信息,读完后参考评论,自己一点一点从msdn中参考资料,整理了新脚本。本来以为会简单,但是实际操作起来,细节会打败人的,评论中的朋友会提到很多实际业务中遇到的问题,很有帮助!

 

 

    

计算数据库中各个表的数据量和每行记录所占用空间--添加架构信息-读后感及知识整理,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。