The built-in procedure sp_spaceused can provide some useful information, especially when an object name is specified; great for showing the finer details of that object. For example the MSreplication_options table:

sp_spaceused_table_details

The information for the number of rows and especially index size can be useful when copying the data or determining space requirements when rebuilding indexes, respectively.

And when an object is not specified, information about the entire database is shown, providing some good information. For example:

sp_spaceused

Personally, I like to see more file information and get more granular details about a database and its size compared to the traditional, sp_spaceused. Yes, Microsoft has made some improvements to sp_spaceused in recent versions of SQL Server, however, I like to see the white space and a little more detail about each of the database files belonging to a database, not just the overall available space. Knowing where my allocated space resides will help me plan for growth. So without further adieu here is the script.

And here is some sample output of what this script will show you:

my_sp_spaceused

Now, if you wanted to add more detail to what is already shown, you could look at the contents of sp_spaceused by doing the following…

…to get some further tidbits from sys.partitions, sys.allocation_units, and sys.internal_tables, but I will leave that as an exercise for the reader. 🙂

Enjoy the script and hopefully it assists you in your day-to-day DBA functions as it does myself.


Edited January 12, 2016

I’ve modified the script to include 2 additional columns, one for used space and one for free space of each file as a percentage, which, at-a-glance allows one to see any problem areas quicker. The new script is as follows:


Edited January 13, 2016

I’ve further modified the script above to make it a little more readable giving the same results. This should be the final edit 🙂

An alternative to sp_spaceused