首页 > 代码库 > 计算数据库中各个表的数据量和每行记录所占用空间 转
计算数据库中各个表的数据量和每行记录所占用空间 转
CREATE TABLE #tablespaceinfo ( nameinfo VARCHAR(500) , rowsinfo BIGINT , reserved VARCHAR(20) , datainfo VARCHAR(20) , index_size VARCHAR(20) , unused VARCHAR(20) ) DECLARE @tablename VARCHAR(255); DECLARE Info_cursor CURSORFOR 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 --创建临时表CREATE TABLE [#tmptb] ( TableName VARCHAR(50) , DataInfo BIGINT , RowsInfo BIGINT , Spaceperrow AS ( CASE RowsInfo WHEN 0 THEN 0 ELSE CAST(DataInfo AS decimal(18,2))/CAST(RowsInfo AS decimal(18,2)) END ) PERSISTED )--插入数据到临时表INSERT INTO [#tmptb] ( [TableName] , [DataInfo] , [RowsInfo] ) SELECT [nameinfo] , CAST(REPLACE([datainfo], ‘KB‘, ‘‘) AS BIGINT) AS ‘datainfo‘ , [rowsinfo] FROM #tablespaceinfo ORDER BY CAST(REPLACE(reserved, ‘KB‘, ‘‘) AS INT) DESC --汇总记录SELECT [tbspinfo].* , [tmptb].[Spaceperrow] AS ‘每行记录大概占用空间(KB)‘FROM [#tablespaceinfo] AS tbspinfo , [#tmptb] AS tmptbWHERE [tbspinfo].[nameinfo] = [tmptb].[TableName]ORDER BY CAST(REPLACE([tbspinfo].[reserved], ‘KB‘, ‘‘) AS INT) DESC DROP TABLE [#tablespaceinfo]DROP TABLE [#tmptb]
转自华仔,个人使用
计算数据库中各个表的数据量和每行记录所占用空间 转
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。