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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

2 thoughts on “Find size of each table in database of SQL Server 2005/2008”

  1. 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.

Comments are closed.