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