How to get sizes for tables in a SQL 2008 database

Under the heading of “often needed and never remembered”, I decided to post a quick T-SQl example of how you can retrieve the space used by the various tables in a SQL 2008 database. 

Note, in my select query I am limiting to just the top 20 largest tables.  I am also ordering by the space the data is using in the table.  These can be easily modified to suit the needs.

 

SET NOCOUNT ON
DBCC UPDATEUSAGE(0)
— Table row counts and sizes.
CREATE TABLE #FXtemp
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)
INSERT #FXtemp EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

SELECT top 20 *
FROM   #FXtemp order by cast(replace(data ,’KB’,”) as int) desc
 
DROP TABLE #FXtemp  

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

Submit a Comment

Your email address will not be published.

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!