Created
June 27, 2022 06:52
-
-
Save zikato/061098e7ee24d283f4a86e0f791a338e to your computer and use it in GitHub Desktop.
Return all table sizes, row counts and column counts
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
; -- Previous statement must be properly terminated | |
WITH tableRowCount | |
AS | |
( | |
SELECT | |
ddps.object_id | |
, SUM(ddps.row_count) AS rowCnt | |
, SUM(ddps.used_page_count) * 8. / 1024. / 1024. AS usedSize | |
FROM sys.dm_db_partition_stats ddps | |
WHERE ddps.index_id < 2 | |
GROUP BY ddps.object_id | |
) | |
, columnCount | |
AS | |
( | |
SELECT | |
o.object_id | |
, COUNT(c.column_id) AS columnCount | |
FROM sys.columns AS c | |
JOIN sys.objects AS o ON c.object_id = o.object_id | |
WHERE o.is_ms_shipped = 0 | |
GROUP BY o.object_id | |
) | |
, allIndexSize | |
as | |
( | |
SELECT | |
ddps.object_id | |
, SUM(ddps.used_page_count) * 8. / 1024. / 1024. AS usedSize | |
, SUM(ddps.reserved_page_count) * 8. / 1024. / 1024. AS reservedSize | |
FROM sys.dm_db_partition_stats ddps | |
GROUP BY ddps.object_id | |
) | |
, tableRank AS | |
( | |
SELECT | |
trc.object_id AS Id | |
, OBJECT_NAME(trc.object_id) AS tableName | |
, trc.rowCnt | |
, cc.columnCount | |
, trc.usedSize AS baseUsedSize_GB | |
, ais.usedSize AS wholeUsedSize_GB | |
, ais.reservedSize AS reservedSize_GB | |
--, DENSE_RANK() OVER (ORDER BY trc.usedSize desc) AS orderBase | |
--, DENSE_RANK() OVER (ORDER BY ais.usedSize desc) AS orderWhole | |
--, DENSE_RANK() OVER (ORDER BY trc.rowCnt desc) AS orderRow | |
--, DENSE_RANK() OVER (ORDER BY cc.columnCount desc) AS orderColumn | |
FROM | |
tableRowCount trc | |
JOIN allIndexSize ais | |
ON trc.object_id = ais.object_id | |
JOIN columnCount cc ON trc.object_id = cc.object_id | |
) | |
SELECT | |
tr.Id | |
, OBJECT_SCHEMA_NAME(tr.id) AS schemaName | |
, tr.tableName | |
, FORMAT(tr.rowCnt, 'N0') AS rowCntFormatted | |
, tr.columnCount | |
, tr.baseUsedSize_GB | |
, tr.wholeUsedSize_GB | |
, tr.reservedSize_GB | |
, (tr.reservedSize_GB - tr.wholeUsedSize_GB) * 1024. AS unusedSize_MB | |
--, tr.orderBase | |
--, tr.orderWhole | |
--, tr.orderRow | |
--, tr.orderColumn | |
FROM tableRank tr | |
--WHERE tr.tableName = 'OptionalTableFilter' | |
ORDER BY | |
tr.reservedSize_GB desc | |
OPTION (RECOMPILE) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment