SDU Tools: Finding out how large SQL Server tables are with ListUserTableSizes

In our free SDU Tools for developers and DBAs, we have a number of functions and procedures that help you work with tables and indexes. One question that often arises is about how big tables are. Our tool ListUserTableSizes helps with that.

SQL Server Management Studio (SSMS) has a report that shows you user table sizes, but people often want a list of sizes that they can get programmatically, perhaps for a monitoring tool, or some sort of automation.

ListUserTableSizes takes the following parameters:

@DatabaseName (sysname) is the database to report on

@SchemasToList (nvarchar(max)) is the word ALL (default) or a comma-delimited list of schemas to include

@TablesToList (nvarchar(max)) is the word ALL (default) or a comma-delimited list of tables to include

@ExcludeEmptyTables (bit) allows you to avoid returning empty tables

@IsOutputOrderedBySize (bit) if this is set, the list is returned in order of size descending

The returned columns are SchemaName, TableName, TotalRows, TotalReservedMB, TotalUsedMB, TotalFreeMB

You can see this procedure in action in the main image above, and in this video:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

Leave a Reply

Your email address will not be published.