本文出处:
感谢桦仔 的分享精神!
很多时候我们都需要计算数据库中各个表的数据量和每行记录所占用空间
这里共享一个脚本
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 DataInfo / RowsInfo 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',