SQL Query to retrieve table size (SQL Server)

December 17, 2011

Hello all, its been months since i have written a blog. Its never late than never. So let me start it right away with a post on SQL. This is my first post on SQL and hope you all find it useful.

I am today giving a post on getting the size of the tables in your database. Its very simple and we have a system defined procedure that makes your job easier. The syntax to get this is,

exec sp_spaceused ‘TABLE NAME’

You shall get the metdata information on your table as you query the above.

Lets try to get the size information on all the tables of your whole DB. Wondering how? This is even simpler, as usual to make the job easy we have got a time saving procedure to do it for us. Lets make a query to get the size of all tables,

EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

The question mark replaces the table names of our DB. This query returns mutiple tables of the metadata like size of every table in our database. To get a view of the metadata as a single table the following set of query can be run together,

/* CREATE A TABLE TO STORE THE INFORMATION */

CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

/*INSERTING THE INFORMATION RETRIEVED TO THE TABLE*/

INSERT #t EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

/*VIEW THE INFORMATION FROM THE TABLE & DROPPING THE TABLE*/

SELECT * FROM #t

DROP TABLE #t /*OPTIONAL TO DROP*/

Hope this post was useful to you all. Thanks and feel free to comment and share your ideas.

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 )

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

%d bloggers like this: