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

Friday, September 18, 2009 |


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

2 comments:

Madhivanan said...

As sp_msforeachtable is undocumented, you can avoid it and simulate it using the following methods

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx

Madhivanan

Ritesh Shah said...

it is easy to use and handy too. more readable also. ofcourse you can iterate loop or cursor but I still go for undocumented SP. as it is not giving wrong results and works fine in both (SQL Server 2005/2008). may be it won't work in next version but as long as these two version concern, I would go for this.