Calculate Table Size and row count

  • Comments posted to this topic are about the item Calculate Table Size and row count

  • Improved version!! I added Schema, since we can have more than 1 table with the same name in the DB.

    Note: We don't need the temporary table as a placeholder.

    In any case if needed, DO NOT use the "SELECT * into #temp_table1"; For bigger DATASETS, this cmd is BAD; It'll create a lockout in Tempdb.

    The best way is to create the place holder via DDL (ie. SELECT * into #temp_table1 where 1 = 2) Once the placeholder is created as #temp_table1

    Then issue the insert stmnt :

    INSERT #temp_table1

    SELECT * FROM WhatEverquery

    This will save you lots of headaches

    here's the improved version which can also b Parameterized as a Sproc if needed

    SELECT +'.' +, CONVERT(numeric(30,3),(CONVERT(float, SUM(A.used_page_count)*8)/1024)) as [Table Used Size(MB)],

    CONVERT(numeric(30,3),(CONVERT(float, SUM(A.reserved_page_count)*8)/1024)) as [Table Located Size(MB)],

    (CONVERT(numeric(30,3),(CONVERT(float, SUM(A.reserved_page_count)*8)/1024)) - CONVERT(numeric(30,3),(CONVERT(float, SUM(A.used_page_count)*8)/1024))) as [Table Free Size(MB)] ,


    from sys.dm_db_partition_stats A join sys.all_objects B

    ON A.object_id = B.object_id

    join sys.schemas S

    on B.schema_id = S.schema_id

    where B.type != 'S'

    group by +'.' +, row_count

    order by +'.' + asc

  • These don't work with partitioned tables or indexes. It lists everything on separate rows.

    [font="Courier New"]____________________________________________________________________________________________
    Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog

  • Here is an alternative that gives a bit more info.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 4 posts - 1 through 3 (of 3 total)

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