CREATE TABLE #TableInfo
(
name NVARCHAR(50) ,
rows INT ,
reserved NVARCHAR(20) ,
data NVARCHAR(20) ,
index_size NVARCHAR(20) ,
unused NVARCHAR(20)
);
DECLARE @TableName NVARCHAR(255);
DECLARE @CmdSql NVARCHAR(1000);
DECLARE Info_Cursor CURSOR
FOR
SELECT o.name
FROM dbo.sysobjects o
WHERE o.type = 'u';
OPEN Info_Cursor;
FETCH NEXT FROM Info_Cursor
INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TableInfo
EXEC sp_spaceused @TableName;
FETCH NEXT FROM Info_Cursor INTO @TableName;
END;
CLOSE Info_Cursor;
DEALLOCATE Info_Cursor;
GO
sp_spaceused @UpdateUsage = 'TRUE';
SELECT *
FROM #TableInfo
ORDER BY CAST(LEFT(LTRIM(RTRIM(reserved)), LEN(LTRIM(RTRIM(reserved))) - 2) AS INT) DESC ,
rows DESC;
DROP TABLE #TableInfo;
