While reading my SQL Server 2005 MCTS book, I came across sp_helpdb. This is a database engine stored procedure which can provide you with information about the databases in your system. You can execute the stored procedure within management studio using:
sp_helpdb
This will return the following results set.
As you can see, it has given information about each database – including db_size of the database. The status containing more information about the database, the status for AdventureWorks is:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Latin1_General_CS_AS, SQLSortOrder=0, IsAnsiNullsEnabled, IsAnsiPaddingEnabled, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsNullConcat, IsQuotedIdentifiersEnabled
We cam also execute it for just a single database.
sp_helpdb @dbname = ‘AdventureWorks’
This will return the same information as above for the table, but we also get information about the actual filegroups for the database, for AdventureWorks we just have the primary filegroup for the data and the transaction log. This can also be obtained by executing sp_helpfile.
This can also be obtained by using the Report button on the Summary tab for a database within SQL Management Studio. The report we are interested in is the Disk Usage report.
This will display the information in a nice table view, however takes a lot longer to execute and process than the raw queries.
The above report also gives information about Disk space used by tables (along with a few other items). This can be obtained by executing sp_spaceused which will return the information on a database as a whole. If you are interested in a particular table, then you can set it as a parameter.
EXEC sp_spaceused @objname = ‘HumanResources.Employee’.
