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

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

参考文章:

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_ONLYFOR    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;    ENDCLOSE 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中参考资料,整理了新脚本。本来以为会简单,但是实际操作起来,细节会打败人的,评论中的朋友会提到很多实际业务中遇到的问题,很有帮助!