Make your own free website on Tripod.com

Stored procedure to identify the top n biggest tables in a database
Last updated: February 2nd '02 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New

NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line
See also:    
  Database programming guidelines, conventions SQL Server DBA and developer interview questions
  Evaluation of federated database servers SQL Server FAQ on programming, designing, administration, tools, replication
  SQL Server security best practices SQL Server administration best practices

As a DBA, what are you gonna do, if your boss or IT manager walks up to you and asks for a list of the top 10 biggest tables in your production database? Well, you can use sp_spaceused, but it can report the space used by any one given table, but not a list of top n biggest tables in your database.

This stored procedure sp_show_huge_tables lists the all the tables in your database in the descending order of their sizes (that is bigger tables first), along with their row counts. You can limit the output to top top n tables. That is you can generate a list of top 10 or top 5 biggest tables in your database. Optionally you can even include system tables in the output. By default, this procedure ignores system tables.

Click here to download the stored procedure sp_show_huge_tables

Usage:
To list all the user tables in the database along with their sizes:
EXEC sp_show_huge_tables

To see the top three biggest tables in your database:
EXEC sp_show_huge_tables 3

To list all the user AND system tables in the database along with their sizes:
EXEC sp_show_huge_tables @include_system_tables = 1


To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1

Like it? Don't like it? Click here to send your comments!


Disclaimer and terms of use
Copyright 1997 - 2002 Narayana Vyas Kondreddi. All rights reserved.