Post reply

See the size of tables using t-sql

  • January 20, 2016 at 1:55 am

    #399590

    Hi,

    I have a list of tables (several tables) and I would like to know their size.

    Is there a way to know that using t-sql?

    It will be easyer then having to select each one from the diagram or having to look at the reports, one by one...

    Thank you

  • January 20, 2016 at 1:56 am

    #1853050

    sp_spaceused 'TableName'

    or query sys.dm_db_index_physical_stats

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 20, 2016 at 4:57 am

    #1853093

    Thank you Gail

  • January 20, 2016 at 5:06 am

    #1853094

    river1 (1/20/2016)

    It will be easyer then having to select each one from the diagram or having to look at the reports, one by one...

    If you right-click on the database name, you can get a size report for all tables.

    John

  • January 21, 2016 at 10:53 pm

    #1853703

    Exec sys.sp_MSforeachtable ' sp_spaceused "?" '

  • January 22, 2016 at 2:03 pm

    #1853923

    river1 (1/20/2016)

    Hi,

    I have a list of tables (several tables) and I would like to know their size.

    Is there a way to know that using t-sql?

    It will be easyer then having to select each one from the diagram or having to look at the reports, one by one...

    Thank you

    WHICH size? Reserved? Used? Data Size? Index Size? Unused?

    Don't answer that. It's a rhetorical question.

    Rather than messing around with sp_space used or some unsupported stored procedure that runs a cursor in the background, open the code for sp_SpaceUsed and see how they did it. Then, convert it to a set based wonder of your own making to work against one or all of the tables in the current database.

    I'd post my own creation in this area but then the only thing you'd learn is how to use my stuff. If you take the time to look at the code of sp_SpaceUsed, you're going to learn and remember stuff that will help you throughout your career.

    --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 23, 2016 at 9:47 am

    #1854012

    Jeff Moden (1/22/2016)

    river1 (1/20/2016)

    Hi,

    I have a list of tables (several tables) and I would like to know their size.

    Is there a way to know that using t-sql?

    It will be easyer then having to select each one from the diagram or having to look at the reports, one by one...

    Thank you

    WHICH size? Reserved? Used? Data Size? Index Size? Unused?

    Don't answer that. It's a rhetorical question.

    Rather than messing around with sp_space used or some unsupported stored procedure that runs a cursor in the background, open the code for sp_SpaceUsed and see how they did it. Then, convert it to a set based wonder of your own making to work against one or all of the tables in the current database.

    I'd post my own creation in this area but then the only thing you'd learn is how to use my stuff. If you take the time to look at the code of sp_SpaceUsed, you're going to learn and remember stuff that will help you throughout your career.

    Hmmm. I wrote a procedure to get the space for all the tables in a database, but it's a wrapper to sp_spaceused to build each row. Now you've got me thinking I really need to dig into sp_spaceused itself.

    Thanks a lot, Jeff. Now I have something else to do. 😛 That's okay though, I know I'll have fun with it.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

Viewing 7 posts - 1 through 6 (of 6 total)

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