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

使用前需要用 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];