How to get list of all tables with sizes in SQL server

There is system table which gives you all table information.

select * from sys.tables

But to get size you have to use sp_spaceused.

following little TSQL gives you all table list with size, index size and unllocated.

--sp_helptext spPersistCheckoutInfo
USE master
DECLARE @TABLE AS TABLE (NAME VARCHAR(100), [ROWS] INT,RESERVED VARCHAR(100), DATA VARCHAR(100), INDEXSIZE VARCHAR(100), UNALLOCATED VARCHAR(100))
DECLARE TABLE_CURSOR CURSOR FOR SELECT NAME FROM SYS.tables
DECLARE @TABLE_NAME VARCHAR(100)
OPEN TABLE_CURSOR
FETCH NEXT FROM TABLE_CURSOR INTO @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @TABLE EXEC(' SP_SPACEUSED ' + @TABLE_NAME )
FETCH NEXT FROM TABLE_CURSOR INTO @TABLE_NAME
END
CLOSE TABLE_CURSOR
DEALLOCATE TABLE_CURSOR
SELECT * FROM @TABLE ORDER BY [ROWS] DESC
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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s