CREATE VIEW Metadata.IndexPhysicalStats
AS
SELECT o.type COLLATE SQL_Latin1_General_CP1_CI_AS AS ObjectType,
s.name AS SchemaName,
o.name AS ObjectName,
i.name AS IndexName,
i.index_id AS IndexId,
i.is_primary_key AS IsPrimaryKey,
i.is_unique AS IsUnique,
i.is_unique_constraint AS IsUniqueConstraint,
p.PartitionCnt,
p.partition_number AS PartitionNumber,
p.ROWS AS PartitionRows,
ps.index_depth AS DEPTH,
ps.index_level AS LEVEL,
ps.record_count AS LevelRows,
ps.page_count AS Pages,
CAST(ps.avg_page_space_used_in_percent AS NUMERIC(6, 2)) AS PagePercentFull,
ps.min_record_size_in_bytes AS MinLen,
ps.max_record_size_in_bytes AS MaxLen,
ps.avg_record_size_in_bytes AS AvgLen,
i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
ds.name COLLATE SQL_Latin1_General_CP1_CI_AS AS DataSpace,
ds.type COLLATE SQL_Latin1_General_CP1_CI_AS AS DataSpaceType,
p.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS DataCompression,
o.OBJECT_ID
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN sys.indexes i (NOLOCK) ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.data_spaces ds (NOLOCK) ON i.data_space_id = ds.data_space_id
CROSS APPLY (SELECT TOP 1
*,
COUNT(p.index_id) OVER (PARTITION BY (SELECT 0)) AS PartitionCnt
FROM sys.partitions p (NOLOCK)
WHERE p.OBJECT_ID = o.OBJECT_ID
AND p.index_id = i.index_id
AND p.ROWS > 0
ORDER BY p.ROWS DESC) p
CROSS APPLY Metadata.GetIndexPhysicalStats(DB_ID(), o.OBJECT_ID, i.index_id, p.partition_number, 'DETAILED') ps
WHERE i.index_id > 0
AND o.type IN ('U','V')
AND i.is_disabled = 0
AND ds.type IN ('FG', 'PS')
AND o.is_ms_shipped = 0