sp_GetAllTableSizes

use master go CREATE PROCEDURE sp_GetAllTableSizes AS DECLARE @tabname varchar(128) --Cursor to get the name of all user tables from the sysobjects listing DECLARE table_cur CURSOR FAST_FORWARD FOR SELECT [name] FROM dbo.sysobjects (NOLOCK) WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1 --A procedure level temp table to store the results CREATE TABLE #TempTable ( tableName varchar(128), numberofRows int, reservedSize varchar(50), dataSize varchar(50), indexSize varchar(50), unusedSize varchar(50) ) --Open the cursor OPEN table_cur --Get the first table name from the cursor FETCH NEXT FROM table_cur INTO @tabname --Loop until the cursor was not able to fetch WHILE (@@Fetch_Status >= 0) BEGIN --Dump the results of the sp_spaceused query to the temp table INSERT #TempTable EXEC sp_spaceused @tabname --Get the next table name FETCH NEXT FROM table_cur INTO @tabname END --Get rid of the cursor CLOSE table_cur DEALLOCATE table_cur --Select all records so we can use the reults SELECT * FROM #TempTable ORDER BY numberofRows DESC --Final cleanup! DROP TABLE #TempTable GO