SQL Server – Show Data and Disk Use Breakdown by Table

database-sizesql serversql-server-2008-r2ssms

I have a SQL Server 2008 R2 database being used by several deployed programs.

Question: Is there an easy way to display how much space each table consumes, for all of the tables in the database, and distinguish logical space from disk space?

If I use SSMS (Management Studio), the storage properties shown for the database reads 167 MB with 3 MB "available" (about the right size, but I'm concerned about the 3 MB available – is this a limit to be concerned about, assuming I know I have enough disk space?)

I can drill into each table, but that takes forever to do.

I know I can write my own queries and test around, but I'd like to know if there's already an easy (built-in?) way to do this.

Best Answer

In SSMS, right-click on the database and go to "Reports", "Standard Reports", "Disk Usage by Table". It will tell you the total size, the data size, the index size, and the unused size for each table (as well as the row count).

Related Question