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