List table space usage and row counts

  • Comments posted to this topic are about the item List table space usage and row counts

    ----------------------------------------------------------------------------------------------
    Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
    Email: Louis.Li@rrlminc.com | Blog[/url] | LinkedIn[/url]

  • Many thanks, this is much cleaner than my previous code.

    I did add

    AND INDEX_ID = 0

    to the WHERE clause to keep my name list unique and not show indexes. I mainly needed the table info.

  • I would use AND INDEX_ID <= 1 when index space is not needed.

    Index ID 0 is for heap tables and Index ID 1 is for tables with clustered index.

    ----------------------------------------------------------------------------------------------
    Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
    Email: Louis.Li@rrlminc.com | Blog[/url] | LinkedIn[/url]

  • tskelley (1/17/2014)


    Many thanks, this is much cleaner than my previous code.

    I did add

    AND INDEX_ID = 0

    to the WHERE clause to keep my name list unique and not show indexes. I mainly needed the table info.

    I added

    AND INDEX_ID in (0,1)

    so it shows both tables with and without clustered indexes. (0=heap, 1=clustered index).

  • As mentioned here there are indexes shown in the results. Would it make sense if I were to group by the "Name" and do a SUM(reserved_page_count * 8) as space_used_kb to get the total space used for the table (including indexes), and do a MAX(row_count) as row_count to get the number of records?

  • mikeg13 (1/17/2014)


    As mentioned here there are indexes shown in the results. Would it make sense if I were to group by the "Name" and do a SUM(reserved_page_count * 8) as space_used_kb to get the total space used for the table (including indexes), and do a MAX(row_count) as row_count to get the number of records?

    Yes, I can see the value of having sum. I had made another script and posted on my blog, check it out if you're interested. Thank you for your suggestion.

    Table Space and row count – Version 2[/url]

    ----------------------------------------------------------------------------------------------
    Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
    Email: Louis.Li@rrlminc.com | Blog[/url] | LinkedIn[/url]

  • The total space used in version 2 doesn't match any of the space used values in version 1. The values in version 1 seem to be considerably higher in all cases. Is this because something is being eliminated from the results calculations? The row counts match.

    Dana

  • danaanderson (1/18/2014)


    The total space used in version 2 doesn't match any of the space used values in version 1. The values in version 1 seem to be considerably higher in all cases. Is this because something is being eliminated from the results calculations? The row counts match.

    Dana

    Thanks Dana for pointing it out, I made a mistake. It has been fixed now.

    ----------------------------------------------------------------------------------------------
    Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
    Email: Louis.Li@rrlminc.com | Blog[/url] | LinkedIn[/url]

  • Louis&Sql (1/18/2014)


    danaanderson (1/18/2014)


    The total space used in version 2 doesn't match any of the space used values in version 1. The values in version 1 seem to be considerably higher in all cases. Is this because something is being eliminated from the results calculations? The row counts match.

    Dana

    Thanks Dana for pointing it out, I made a mistake. It has been fixed now.

    You're welcome. Ah, I should have noticed the missing [*8] in the calculations.

    Dana

  • Please excuse my ignorance.

    I need to change this how?

    I get the error

    Incorrect syntax near 'o'.

    I tried changing SCHEMA_NAME to the name of the schema for the particular database. That didn't help.

  • Carolyn S. White (1/20/2014)


    Please excuse my ignorance.

    I need to change this how?

    I get the error

    Incorrect syntax near 'o'.

    I tried changing SCHEMA_NAME to the name of the schema for the particular database. That didn't help.

    What happens if you run this script under admin privilege?

    ----------------------------------------------------------------------------------------------
    Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
    Email: Louis.Li@rrlminc.com | Blog[/url] | LinkedIn[/url]

  • The same thing.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'o'.

    I'm trying this with sqlserver 2012

  • Can you copy / paste the script from your SSMS here?

    ----------------------------------------------------------------------------------------------
    Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
    Email: Louis.Li@rrlminc.com | Blog[/url] | LinkedIn[/url]

  • I literally copied and pasted the code verbatim from your post as a new query on the database I wanted to get the results on.

    Does it work on an entire database? or is it for a single table?

    I'm literally ignorant on this.

    If it is too much trouble to educate me, it is ok to say so.

  • Carolyn S. White (1/20/2014)


    I literally copied and pasted the code verbatim from your post as a new query on the database I wanted to get the results on.

    Does it work on an entire database? or is it for a single table?

    I'm literally ignorant on this.

    If it is too much trouble to educate me, it is ok to say so.

    Actually I'm interested in knowing why it failed.

    To answer your question - Yes, it works on entire database

    And it works with SQL Server 2012.

    You don't need to change anything to be able to run this script. It should work on its own.

    ----------------------------------------------------------------------------------------------
    Microsoft Certified Solution Master: Data Platform, Microsoft Certified Trainer
    Email: Louis.Li@rrlminc.com | Blog[/url] | LinkedIn[/url]

Viewing 15 posts - 1 through 15 (of 21 total)

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