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 ;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s