SDU Tools: List User Table and Index Sizes in SQL Server
I’m often wondering about how large different objects in my databases are. There are reports to provide that info, but I often want it in a programmatic form. One of our free SDU Tools for developers and DBAs does just that. It’s ListUserTableAndIndexSizes.
You can see how to execute it in the main image above. The procedure takes these parameters:
@DatabaseName sysname - This is the database to process @SchemasToList nvarchar(max) -> ‘ALL’ or comma-delimited list of schemas to list @TablesToList nvarchar(max) -> ‘ALL’ or comma-delimited list of tables to list @ExcludeEmptyIndexes bit -> 0 for list all, 1 for don’t list empty objects @ExcludeTableStructure bit -> 0 for list all, 1 for don’t list base table (clustered index or heap) @IsOutputOrderedBySize bit -> 0 for alphabetical, 1 for size descending
One row is returned for each user table or index.
The columns returned are SchemaName, TableName, IndexName, TotalRows, TotalReservedMB, TotalUsedMB, TotalFreeMB.
You can see it in action here:
To become an SDU Insider and to get our free tools and eBooks, please just visit here:
http://sdutools.sqldownunder.com
Learn about indexing
Want to learn more about indexes? Take our online on-demand course now: https://training.sqldownunder.com/p/sql-server-indexing-for-developers
2018-12-26