Quick Table and Index Size Script

This script from Matthew L. Kearns quickly returns table and index disk space and row counts for all tables in the current database. Essentially, the script puts the table names into a cursor and then spits out sp_spaceused results into a temp table. The contents of the temp table can be displayed in the sort order desired by the user.

SET NOCOUNT ON

CREATE TABLE #TBLSize
 (Tblname varchar(80), 
 TblRows int,
 TblReserved varchar(80),
 TblData varchar(80),
 TblIndex_Size varchar(80),
 TblUnused varchar(80))

DECLARE @DBname varchar(80) 
DECLARE @tablename varchar(80) 

SELECT @DBname = DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database):   ' + @@ServerName + ' / ' + @DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR 
SELECT NAME 
FROM sysobjects
WHERE xType = 'U'

OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor 
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
   INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused)
   EXEC Sp_SpaceUsed @tablename
      
   -- Get the next author.
   FETCH NEXT FROM TblName_cursor 
   INTO @tablename
END

CLOSE TblName_cursor
DEALLOCATE TblName_cursor

SELECT  CAST(Tblname as Varchar(30)) 'Table',
	CAST(TblRows as Varchar(14)) 'Row Count',
	CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)',
        CAST(TblData as Varchar(14)) 'Data Space',
	CAST(TblIndex_Size  as Varchar(14)) 'Index Space',
        CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Total Space (KB)' Desc

PRINT ''
PRINT 'By Table Name Alphabetical'


SELECT  CAST(Tblname as Varchar(30)) 'Table',
	CAST(TblRows as Varchar(14)) 'Row Count',
	CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)',
        CAST(TblData as Varchar(14)) 'Data Space',
	CAST(TblIndex_Size  as Varchar(14)) 'Index Space',
        CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Table'

DROP TABLE #TblSize