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.

Filed under: Microsoft SQL Server, Reporting, SQLServerPedia Syndication, SSMS Tagged: microsoft sql server, Reporting, SSMS