Default Database Reports – Disk Usage by Table4
July 19, 2017 by Kenneth Fisher
Every now and again you need to know how big a table is. Or several tables. Or all of the tables. Number of rows is frequently handy when you’re going to create a new index or otherwise modify the table. The amount of space used by the indexes can be helpful in deciding how much space you need to do a re-index. The tables with the most unused space is nice to know if you have a problem with ever growing heaps.
In the past my go to solution here was sp_spaceused. It’s a really handy procedure.
USE AdventureWorks2014; GO EXEC sp_spaceused 'Person.Person'; GO
Great information but it has a few problems. You can only run it for one table at a time (sp_msforeachtable is a workaround, if undocumented), the file sizes aren’t consistent (sometimes KB, sometimes MB or even GB), and it only returns the name of the object but not the schema. So if there is the same table name under multiple schemas it can get tricky.
A much better solution is the default database report “Disk Usage by Table”. Right click on the database -> Reports -> Standard Reports -> Disk Usage by Table.
And you get a very nice report with all of the tables listed, with schema names and consistent sizes.
It’s even sortable! Unfortunately, the arrows next to the columns always start with ascending but if you click on the arrow next to the Unused column twice you will get the tables with the most unused space.
You can ignore the orange/gray lines. For whatever reason each line doesn’t change from it’s initial color when you sort the report.
Category: Microsoft SQL Server, Reporting, SQLServerPedia Syndication, SSMS | Tags: microsoft sql server, Reporting, SSMS
4 thoughts on “Default Database Reports – Disk Usage by Table”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] Kenneth Fisher shows off my favorite built-in SSMS report: […]
[…] been using sp_spaceused a lot recently so I thought I would bring it up again. Basically it’s a quick way to bring up the size of things. […]
On SQL Server 2016, the default database reports is greyed out. How to run the Disk Usage By tables report on SQL Server 2016?
It’s really more dependent on the SSMS you are using. What version of SSMS? Is it 16 or 17?