Table Usage Report

  • Hi,

    I have found a script which gives me disk size of all tables:

    SELECT TOP 100

    -- (row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,

    a3.NAME AS [schema]

    ,a2.NAME AS

    ,a1.rows AS [rowCount]

    ,(a1.reserved + ISNULL(a4.reserved, 0)) * 8 AS [reserved]

    ,a1.data * 8 AS [data]

    ,(

    CASE

    WHEN (a1.used + ISNULL(a4.used, 0)) > a1.data

    THEN (a1.used + ISNULL(a4.used, 0)) - a1.data

    ELSE 0

    END

    ) * 8 AS [indexSize]

    ,(

    CASE

    WHEN (a1.reserved + ISNULL(a4.reserved, 0)) > a1.used

    THEN (a1.reserved + ISNULL(a4.reserved, 0)) - a1.used

    ELSE 0

    END

    ) * 8 AS [unused]

    --,CONVERT(VARCHAR, SYSUTCDATETIME(), 103) + ' ' + left(CONVERT(VARCHAR, SYSUTCDATETIME(), 108), 5) AS [date]

    FROM (

    SELECT ps.object_id

    ,SUM(CASE

    WHEN (ps.index_id < 2)

    THEN row_count

    ELSE 0

    END) AS [rows]

    ,SUM(ps.reserved_page_count) AS reserved

    ,SUM(CASE

    WHEN (ps.index_id < 2)

    THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)

    ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)

    END) AS data

    ,SUM(ps.used_page_count) AS used

    FROM sys.dm_db_partition_stats ps

    GROUP BY ps.object_id

    ) AS a1

    LEFT OUTER JOIN (

    SELECT it.parent_id

    ,SUM(ps.reserved_page_count) AS reserved

    ,SUM(ps.used_page_count) AS used

    FROM sys.dm_db_partition_stats ps

    INNER JOIN sys.internal_tables it

    ON (it.object_id = ps.object_id)

    WHERE it.internal_type IN (

    202

    ,204

    )

    GROUP BY it.parent_id

    ) AS a4

    ON (a4.parent_id = a1.object_id)

    INNER JOIN sys.all_objects a2

    ON (a1.object_id = a2.object_id)

    INNER JOIN sys.schemas a3

    ON (a2.schema_id = a3.schema_id)

    WHERE a2.type <> N'S'

    AND a2.type <> N'IT'

    ORDER BY a1.ROWS DESC

    I was wondering if any has a script which will give me a similar report and a percentage figure of how much each table has grown since past week or month.

    Thanks in advance

  • 1) I bet some third party systems can do this.

    2) It is VERY easy to roll you own historical trending by just putting your results into a permanent table. HIGHLY recommended!

    3) I don't know that SQL Server tracks this historically for you.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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