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
| Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line |
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!