Find size of each table in database of SQL Server 2005/2008

Well, recently I have observed that data file of my database started growing like anything and I wonder which table(s) is causing this. Generally by looking at datafile you can’t assume that which table(s) is growing high so you need to check size of table and index. I decided to first look at the size of table, how can do that? Well there is one stored procedure in SQL Server which is really very handy in this scenario.

use adventureworks

go

--look at overall scenario about total size of table

--and index and database size etc.

EXEC sp_spaceused

--now let us look at the size of perticular table

EXEC sp_spaceused 'Production.ProductProductPhoto'

Last T-SQL statement would give you total number of rows in table, reserved size, data size, index size and unused space. WOW, really quick and handy SP, isn’t it????

But this SP will give you information about just one table and what, if you want to look at the details about all tables in your database.

There are two ways to go for in this scenario.

1.)    Use following T-SQL which uses SP_MSForEachTable (undocumented SP), If you want to know more about this SP, click here to look at my past article.

exec sp_MSforeachtable @command1='print ''?'' exec sp_spaceused ''?'''

2.)    Another way is really easy to go for, it’s a readymade graphical report provided by SQL Server itself. Just open up your SSMS, right click on database, Select Reports, click on Standard Reports and click on “Disk Usage by Top Tables”. There are so many other useful report too, which you can study and can use whenever you need it.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com