Listing all tables in a MSSQL Database and their row counts and space uses

Using sp_spaceused

sp_spaceused without parameters displays the disk space reserved and used by the whole database. However by specifying a table name as the first parameter it will display the number of rows, disk space used and reserved by a table. We can use this with the sp_MSForEachTable procedure mentioned above to get results for every table. An advantage to this approach is that it also shows the space used each table (data and index).

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),      
       reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), 
       unused VARCHAR(18))

EXEC       sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT     TableName,CONVERT(bigint,rows) AS NumberOfRows,
           CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
FROM       #RowCountsAndSizes 
ORDER BY   NumberOfRows DESC,SizeinKB DESC,TableName

DROP TABLE #RowCountsAndSizes

Hope the content is helpful.

Thanks