Post reply

Top 5 tables by size

  • January 30, 2007 at 10:50 pm

    #153408

    Hi all,

       Here is my query that how to get the top 5 tables by size for a particular database?

    Would any one provide a dynamic query for that?

    Any suggestions would be helpful to me..

    Thanks,

    Bagath.

  • January 30, 2007 at 11:16 pm

    #686731

    Try this:

    Script to analyze table space usage

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

     

     

     

  • January 30, 2007 at 11:30 pm

    #686733

    Thanks You very much ...:-)

     

    Cheers,

    Bagath

  • January 31, 2007 at 6:27 am

    #686808

    Hey Micheal!  Welcome aboard!  Have seen lot's of your posts on the "other" forum... glad to see another intelligent person come on (well, except for the cursor )

     

    By the way... if you write a long post, be sure to do a "copy" before you try to submit your post... dunno what it is but after a time, this forum post engine seems to "lose it's mind" and all will be lost when you finally go to post.  Always do a "copy" of the text in your post before you submit it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 31, 2007 at 6:31 am

    #686809

    try this

    SELECT TOP 5 OBJECT_NAME(id),ROWS FROM sysindexes ORDER BY [ROWS] desc

  • January 31, 2007 at 12:31 pm

    #686940

    Well, somtimes you just need a cursor.

    There are no dumb tools, just dumb workmen. 

     

     

  • January 31, 2007 at 9:10 pm

    #687032

    Hey,

    Thanks allot for your great responce and suggestions.Will try to continue like that only.

    Once again thanks to all of you.

    Cheers,

     

  • February 1, 2007 at 1:16 pm

    #687210

    Actually, if you have more than one index on a large table, you'll get back duplicate results.

    eg: The results from one of our databases

    name rows

    ---- ----

    fr_extract 5668928

    fr_extract 5668928

    fr_extract 5668928

    fr_extract 5668928

    invoiceqty04 4330914

    Try this instead; pick up the maximum rows indexed for each table, and then pick up the top 5 tables:

    select top 5 [name], [rows]

    from (

    SELECT OBJECT_NAME(id) [name], max(rows) [rows]

    FROM [sysindexes]

    group by OBJECT_NAME(id)

    ) inner_query

    order by rows desc

    Rick
    townsends.ca

  • February 2, 2007 at 3:09 am

    #687339

    If you want to have the top 5 biggest table (by szize and not by the number of rows) then you can use this one:

    select  top 5

     O.name,

     Pages = reserved,

     KB_used = reserved * 8

    from sysindexes I join sysobjects O on O.id = I.id

    where indid < 2

    order

    by reserved desc

    But don't forget: this is not 100% up to date! To have a more relevant information you should run dbcc updateusage before



    Bye
    Gabor

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply