There are often times that I need to do checks on the space used by databases on the server, and in particular, the tables in those databases. This is a script that I quickly put together that will return the following information for each of the tables in the database on which the script is run. The following information is returned:
- Table Name
- Row Count
- Reserved Space (KB)
- Data space used (KB)
- Index size (KB)
- Unused space (KB)
The above information is gathered using sp_spaceused. Now I’m sure that there are a number of other ways to get this information, but this was quick and easy to produce and use. If you have anything that you’d like to contribute, I’d love to hear it.
Copy. Paste. Enjoy.
**UPDATE – 2011/09/14**
My friend Jim McLeod (Blog | Twitter) has informed me that there is a better way to get the space used and row count details for objects in your database. His script utilises the sysindexes table, which means that this is compatible across a wider set of SQL Server versions including 2000/2005/2008. His original script appears in the comments below. I have reproduced it here with a little formatting, commenting and an extra table to restrict the results to just *user* tables:
I’ve left the first script in the post for comparison. Thanks Jim!