Display the size of all tables in Sql Server 2005


You can use sys.SP_SpaceUsed stored procedure to get the size of all the tables.

Below query will calulate the space of all the tables.
–create temp table to store the result
CREATE TABLE #temptable
  (
     tablename    VARCHAR(100),
     numberofrows VARCHAR(100),
     reservedsize VARCHAR(50),
     datasize     VARCHAR(50),
     indexsize    VARCHAR(50),
     unusedsize   VARCHAR(50)
  )

–Inserting into temp table

INSERT INTO #temptable
            (tablename,
             numberofrows,
             reservedsize,
             datasize,
             indexsize,
             unusedsize)

EXEC Sp_msforeachtable @command1=“EXEC sp_spaceused ‘?'”

SELECT *
FROM   #temptable

— drop the temporary table
DROP TABLE #temptable 

 

Size of All the tables

 

 

1 thought on “Display the size of all tables in Sql Server 2005

Leave a Reply to JD Cancel 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 )

Connecting to %s