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

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

2024-07-13 17:36:21   212人阅读

参考文章:

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

 

 

　　　　


联系
我们