Table Space Used in DB

  • Luis Chiriff

    Old Hand

    Points: 312

    Comments posted to this topic are about the item Table Space Used in DB

  • Yanko Nikolov

    SSC Enthusiast

    Points: 187

    Nice article, but in MS SQL Server is preferred to avoid courses. With the System Dynamic Management Views this is now a hard to write select statement that return size for all tables in database.
    For example:

    ;with tbl_pages_counts as
    (
      select object_id, index_id, row_count, used_page_count, reserved_page_count, fp.free_page_count
      from sys.dm_db_partition_stats
      cross apply ( select reserved_page_count - used_page_count as free_page_count ) as fp
    )
    select  B.name
      ,  A.row_count
      ,  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.free_page_count)*8)/1024))  as [Table Free Size(MB)]
    from tbl_pages_counts as A
    inner join sys.all_objects as B
      on A.object_id = B.object_id and B.type = N'U'
    group by name, row_count
    order by [Table Located Size(MB)] desc
    -- order by A.row_count desc
    -- order by B.name
    go

  • Luis Chiriff

    Old Hand

    Points: 312

    Your script is good, however you always need to ensure you include schema in queries like this, when you don't, you run the risk of similarly named objects grouping together and giving a false picture when there are multiple schema's involved, otherwise your script is only good for single schema'd databases.

    Using your example, this would be

    ;with TBL_PAGES_COUNTS as
    (
    SELECT
        Object_ID
        ,Index_ID
        ,Row_Count
        ,Used_Page_Count
        ,Reserved_Page_Count
        ,FP.Free_Page_Count
    FROM
        SYS.DM_DB_PARTITION_STATS
        CROSS APPLY (SELECT Reserved_Page_Count - Used_Page_Count as Free_Page_Count) as FP
    )
    SELECT
        SCHEMA_NAME(SCHEMA_ID) as SchemaName
        ,B.Name as TableName
        ,A.Row_Count
        ,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.Free_Page_Count)*8)/1024)) as [Table Free Size(MB)]
    FROM
        TBL_PAGES_COUNTS as A
        INNER JOIN SYS.ALL_OBJECTS AS B ON A.OBJECT_ID = B.OBJECT_ID AND B.TYPE = N'U'
    GROUP BY
        B.Name
        ,B.Schema_ID
        ,A.Row_Count
    ORDER BY [Table Located Size(MB)] DESC
    -- ORDER BY A.Row_Count DESC
    -- ORDER BY B.Name

    go

  • GabyYYZ

    SSCertifiable

    Points: 7913

    Psst...we can still see your table names, not sure if you're okay with that.  It's not 100% opaque..

    Gaby
    ________________________________________________________________
    "In theory, theory and practice are the same. In practice, they are not."
    - Albert Einstein

  • Luis Chiriff

    Old Hand

    Points: 312

    GabyYYZ - Tuesday, August 14, 2018 9:37 AM

    Psst...we can still see your table names, not sure if you're okay with that.  It's not 100% opaque..

    Yup, I know, I did that on purpose. It's all good

    🙂

Viewing 5 posts - 1 through 5 (of 5 total)

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