CREATE VIEW Metadata.TableUsage
AS
SELECT QUOTENAME(s.NAME) + '.' + QUOTENAME(T.name) AS FQN,
s.name AS SchemaName,
t.name AS TableName,
CASE WHEN p.TableType = 1 THEN 'CLUSTERED'
ELSE 'HEAP'
END AS TableDesc,
p.ROWS,
CAST(CASE WHEN p.ROWS > 0 THEN p.UsedPages * 8 * 1000.0 / p.ROWS
END AS NUMERIC(12, 2)) AS AvgBytesPerRow,
p.UsedPages * 8 AS UsedKB,
p.ReservedPages * 8 AS ReservedKB,
p.UsedIndexPages * 8 AS UsedIndexKB,
p.ReservedIndexPages * 8 AS ReservedIndexKB,
(p.UsedPages + ISNULL(p.UsedIndexPages, 0)) * 8 AS TotalUsedKB,
(p.ReservedPages + ISNULL(p.ReservedIndexPages, 0)) * 8 AS TotalReservedKB,
CAST(CASE WHEN p.ReservedPages > 0 THEN p.UsedPages * 100.00 / p.ReservedPages
END AS NUMERIC(6, 2)) AS [Used%],
CAST(CASE WHEN p.ReservedIndexPages > 0 THEN p.UsedIndexPages * 100.00 / p.ReservedIndexPages
END AS NUMERIC(6, 2)) AS [UsedIndex%],
CAST(CASE WHEN (p.ReservedPages + ISNULL(p.ReservedIndexPages, 0)) > 0
THEN (p.UsedPages + ISNULL(p.UsedIndexPages, 0)) * 100.00 / (p.ReservedPages + ISNULL(p.ReservedIndexPages, 0))
END AS NUMERIC(6, 2)) AS [TotalUsed%],
p.NonClustIndCnt,
p.PartitionCount,
t.create_date AS CreateDate,
t.modify_date AS ModifyDate,
t.OBJECT_ID AS ObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON s.SCHEMA_ID = t.SCHEMA_ID
CROSS APPLY (SELECT COUNT(DISTINCT (CASE WHEN ddps.index_id > 1 THEN ddps.index_id
END)) AS NonClustIndCnt,
MAX(CASE WHEN ddps.index_id < 2 THEN ddps.index_id
END) AS TableType,
SUM(CASE WHEN ddps.index_id < 2 THEN ddps.row_count
END) AS ROWS,
SUM(CASE WHEN ddps.index_id < 2 THEN ddps.used_page_count
END) AS UsedPages,
SUM(CASE WHEN ddps.index_id < 2 THEN ddps.Reserved_page_count
END) AS ReservedPages,
SUM(CASE WHEN ddps.index_id < 2 THEN 1
ELSE 0
END) AS PartitionCount,
SUM(CASE WHEN ddps.index_id >= 2 THEN ddps.used_page_count
END) AS UsedIndexPages,
SUM(CASE WHEN ddps.index_id >= 2 THEN ddps.Reserved_page_count
END) AS ReservedIndexPages
FROM sys.dm_db_partition_stats ddps (NOLOCK)
WHERE ddps.OBJECT_ID = t.OBJECT_ID) p
WHERE t.is_ms_shipped = 0