Determine Size of a Table in SQL Server
Ever wonder how big a table really is in your database? You know there are a million rows in the table, but how much space is that really taking?
SQL Server provides a built-in stored procedure that you can run to easily show the size of a table, including the size of the indexes… which might surprise you.
Syntax:
sp_spaceused 'Tablename'
Here's an example of it in action:
Highly useful stuff. It's always interesting to see that the indexes are bigger than the actual data.
The Geek is the founder of How-To Geek and a geek enthusiast. When he's not coming up with great how-to articles, he's probably writing at his personal blog. This article was written on 03/27/07 and tagged with: Database, SQL Server
While this is a useful command for determining the space allocated to a table, keep in mind that sp_spaceused references the sysindexes system table to get the space attributes and sometimes what's stored there can get be out of synch with reality. If you see space numbers that are seemingly out of whack (especially for the 'unused' value), run a "DBCC UPDATEUSAGE" command against the table in question before running sp_spacesued (or against the entire database if you have time and aren't worried about stepping on anyone's toes).
This can automatically be done as an optional parameter within sp_spaceused…
e.g. sp_spaceused 'Orders',true
…or as separate commands as shown below.
To update usage stats for the entire current database, the syntax is:
DBCC UPDATEUSAGE (0)
For a specific, named database:
DBCC UPDATEUSAGE ('InsertdDbNameHere')
For a specific table:
DBCC UPDATEUSAGE ('InsertdDbNameHere','InsertdTableNameHere')
You can always check BOL for all the details on DBCC UPDATEUSAGE
Thank you, using query analyer and running procedure sp_spaceused is helpful, however, what if your database has close too 100 tables. Anyway to return the entire database size?
I just found this, it returns spaceused for each table:
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"