Default Database Reports – Disk Usage by Table

1

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

DiskSpaceReport0

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.

DiskSpaceReport1

And you get a very nice report with all of the tables listed, with schema names and consistent sizes.

DiskSpaceReport2

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.

DiskSpaceReport3

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.

One thought on “Default Database Reports – Disk Usage by Table

  1. […] Kenneth Fisher shows off my favorite built-in SSMS report: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,674 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: