There are many reasons why you would want to know the size of a database that you are getting ready to work with – any bit of information helps put together the picture of what you are about to walk into (or dropped new deep into, depending on your work environment). From a DBAs perspective, knowing the different sizes of tables can assist readily in looking at the storage subsystem and making sure it is configured correctly for the database environment at hand – by no means is it a tell-all, but again, one more piece of information to add to the picture.
SQL 2005 has a few undocumented stored procedures, one of which we will make use of: sp_msforeachtable. One reason why I like this stored proc is that it pulls back the fully qualified object name within the given database; there are quite a few blogs or posts out there that use the information_schema views, which is quite fine, but these quite frequently assume that all objects in the database are using the dbo schema (even though information_schema views have the schema name in there!). I know that a lot of people still develop entire solutions using only the dbo schema (we can take this up later), but for those who are using SQL 2005/2008 to the proper extent and using schemas as part of a security model and data namespaces, then the guys at Microsoft did you a favor by insuring that the sp_msforeachtable stored procedure grabs the fully qualified name as part of the output.
The other piece it puts into practice that is quite handy is placing brackets around the schema and the table name. Why is this handy? I have seen a lot of developers use names for database objects that should be reserved names – SQL Server, just like .NET, won’t stop you from doing this, but it can cause confusion, which is why SQL Server internal processes always put  around the names, assuming that some users will make this mistake. This undocumented procedure follows that same principle – so if you end up using a reserved name for your object, have no fear, the script below still works despite your error in judgment:
declare @SpaceUsed as table
, TableRows int
, Reserved varchar(20)
, Data varchar(20)
, IndexSize varchar(20)
, Unused varchar(20)
insert into @SpaceUsed
exec sp_msforeachtable ‘exec sp_spaceused ”?”’
, ReservedInMB = cast(cast(replace(Reserved, ‘ KB’,”) as decimal(18,4))/1024 as decimal(18,4))
, DataInMB = cast(cast(replace(Data, ‘ KB’,”) as decimal(18,4))/1024 as decimal(18,4))
, IndexSizeInMB = cast(cast(replace(IndexSize, ‘ KB’,”) as decimal(18,4))/1024 as decimal(18,4))
, UnusedInMB = cast(cast(replace(Unused, ‘ KB’,”) as decimal(18,4))/1024 as decimal(18,4))
What can you do with this information? A great many things – part of the answer to that question depends on what prompted you to look at it in the first place. But we can start with one assumption: many people are looking at this information because they are concerned about the size of the objects in the database. This gives a quick purview into the system to find which objects deserve the most attention — these results are an accumulation of all of the space used by an object across all file groups and partitions – it does not list out the space used by an object within different partitions (should you have them). If you want to get more detailed information about a particular object, break open sp_spaceused and you will see how it aggregates the information for each object across all of the logical and physical data spaces for a given database.
Feel free to e-mail with any questions/comments.