How to Find Size of a Table in SQL Server


Have you thought, how big a table really is in your database? You know there are a million rows in the table, but how much space is that really taking?

SQL Server provides a built-in stored procedure that you can run to easily show the size of a table, including the size of the indexes.

Syntax:

sp_spaceused 'Tablename'

Output will be like this,

How to Find Size of a Table in SQL Serve

Or in management studio: Right Click on table -> Properties -> Storage

Use below script to list all tables in a database and their row counts and sizes

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

Output will like this,

How to Find Size of a Table in SQL Serve

Important
SQL Server doesn’t always keep disk usages up to date. Fortunately there is a DBCC command which updates these figures. Just run this command before the script above to make sure the figures are accurate :

DBCC UPDATEUSAGE(0)

<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.