How to: Query All Tables’ Row Count, Data Space & Index Space

Query all tables’ row count, data space & index space in SQL Server

SET NOCOUNT ON;

DBCC UPDATEUSAGE(0);

CREATE TABLE #t
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
);

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''';

SELECT * FROM #t
ORDER BY [name];
 
DROP TABLE #t ;