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
About

hi there, I am software engineer, working in e-commerce company and passionate about all thing digital. On this blog, I share my experiments with different technology.

Posted in SQL Server

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

What people say about post?
seo hizmeti on Awesome wordpress ecommerce…

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 38 other followers

%d bloggers like this: