CREATE VIEW Metadata.IndexDetail
AS
WITH Part
AS (SELECT ps.data_space_id,
pf.boundary_value_on_right,
pf.fanout AS PartitionCnt,
rv.boundary_id AS PartitionNumber,
T.NAME AS TypeName,
rv.[Value],
rv2.VALUE AS PrevValue
FROM sys.partition_functions pf (NOLOCK)
INNER JOIN sys.partition_schemes ps (NOLOCK) ON ps.function_id = pf.function_id
INNER JOIN sys.partition_parameters pp (NOLOCK) ON pp.function_id = pf.function_id
INNER JOIN sys.types t (NOLOCK) ON pp.user_type_id = t.user_type_id
INNER JOIN sys.partition_range_values rv (NOLOCK) ON rv.function_id = pf.function_id
LEFT OUTER JOIN sys.partition_range_values rv2 (NOLOCK) ON rv2.function_id = pf.function_id
AND rv2.boundary_id = rv.boundary_id - 1
AND rv.boundary_id > 1
AND rv2.parameter_id = 1
WHERE rv.parameter_id = 1
AND pp.parameter_id = 1)
SELECT QUOTENAME(s.NAME) + '.' + QUOTENAME(o.name) AS FQN,
s.name AS SchemaName,
o.name AS ObjectName,
i.name AS IndexName,
i.index_id AS IndexId,
i.is_primary_key AS PK,
i.is_unique AS [Unique],
i.is_unique_constraint AS UnqC,
i.IGNORE_DUP_KEY AS IgnoreDup,
i.is_disabled AS Disabled,
p.partition_number AS [Partition],
p7.PartitionCnt,
pt.PrevValue AS PartPreVal,
p7.PartitionValue AS PartVal,
g.PartitionColumn,
rf.PartitionFilter,
i.filter_definition AS IndexFilter,
g.KeyCnt,
g.KeyColumns,
g.IncludeCnt,
g.IncludeColumns,
p.ROWS,
CAST(pss.used_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS UsedMB,
CAST(pss.reserved_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS ReservedMB,
CASE WHEN pss.reserved_page_count > 0 THEN CAST(pss.used_page_count * 100.0 / pss.reserved_page_count AS NUMERIC(6, 2))
END AS [Used%],
CASE WHEN p.ROWS > 0 THEN pss.used_page_count * 8 * 1024 / p.ROWS
END AS BytesPerRow,
CAST(pss.in_row_data_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS InRowDataMB,
CAST(pss.in_row_used_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS InRowUsedMB,
CAST(pss.in_row_reserved_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS InRowReservedMB,
CAST(pss.lob_used_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS LobUsedMB,
CAST(pss.lob_reserved_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS LobReservedMB,
CAST(pss.row_overflow_used_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS RowOverflowUsedMB,
CAST(pss.row_overflow_reserved_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS RowOverflowReservedMB,
p7.BoundaryOnRight,
i.fill_factor AS [FillFactor],
i.is_padded AS PadIndex,
i.ALLOW_ROW_LOCKS AS AllowRowLocks,
i.ALLOW_PAGE_LOCKS AS AllowPageLocks,
p.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS Compression,
o.type COLLATE SQL_Latin1_General_CP1_CI_AS AS ObjectType,
ds.name AS DataSpace,
CASE ds.type
WHEN 'FG' THEN 'FILEGROUP'
WHEN 'PS' THEN 'PARTITION SCHEME'
WHEN 'FD' THEN 'FILESTREAM'
ELSE ds.TYPE
END AS DataspaceDesc,
ds.type COLLATE SQL_Latin1_General_CP1_CI_AS AS DataSpaceType,
i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
i.data_space_id AS DataSpaceId,
p.hobt_id AS HobtId,
o.OBJECT_ID AS ObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
INNER JOIN sys.indexes i (NOLOCK) ON i.OBJECT_ID = o.OBJECT_ID
LEFT OUTER JOIN sys.data_spaces ds (NOLOCK) ON i.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.partitions p (NOLOCK) ON p.OBJECT_ID = o.OBJECT_ID
AND p.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_partition_stats (NOLOCK) pss ON pss.OBJECT_ID = o.OBJECT_ID
AND pss.partition_id = p.partition_id
AND pss.index_id = i.index_id
AND p.partition_number = pss.partition_number
OUTER APPLY (SELECT Util.dbo.StringConcat(CASE WHEN partition_ordinal = 1 THEN ColumnName
END, ',') AS PartitionColumn,
Util.dbo.StringConcat(CASE WHEN key_ordinal > 0 THEN ColumnName + CASE WHEN is_descending_key = 1 THEN ' DESC'
ELSE ''
END
END, ',') AS KeyColumns,
SUM(CASE WHEN key_ordinal > 0 THEN 1
ELSE 0
END) AS KeyCnt,
Util.dbo.StringConcat(CASE WHEN is_included_column > 0 THEN ColumnName
END, ',') AS IncludeColumns,
SUM(CASE WHEN is_included_column > 0 THEN 1
ELSE 0
END) AS IncludeCnt
FROM (SELECT TOP 999999
ic.partition_ordinal,
ic.key_ordinal,
ic.is_included_column,
ic.is_descending_key,
c.name AS ColumnName
FROM sys.columns c (NOLOCK)
INNER JOIN sys.index_columns ic (NOLOCK) ON ic.column_id = c.column_id
WHERE c.OBJECT_ID = o.OBJECT_ID
AND ic.OBJECT_ID = o.OBJECT_ID
AND ic.index_id = i.index_id
ORDER BY ic.key_ordinal,
index_column_id) k) g
LEFT OUTER JOIN Part pt ON ds.TYPE = 'PS'
AND pt.data_space_id = i.data_space_id
AND pt.PartitionNumber = p.partition_number
OUTER APPLY (SELECT TOP 1
ps.data_space_id,
pf.boundary_value_on_right,
pf.fanout AS PartitionCnt,
rv.boundary_id AS PartitionNumber,
T.NAME AS TypeName,
rv.[Value]
FROM sys.partition_functions pf (NOLOCK)
INNER JOIN sys.partition_schemes ps (NOLOCK) ON ps.function_id = pf.function_id
INNER JOIN sys.partition_parameters pp (NOLOCK) ON pp.function_id = pf.function_id
INNER JOIN sys.types t (NOLOCK) ON pp.user_type_id = t.user_type_id
INNER JOIN sys.partition_range_values rv (NOLOCK) ON rv.function_id = pf.function_id
WHERE rv.parameter_id = 1
AND pp.parameter_id = 1
AND ds.TYPE = 'PS'
AND ps.data_space_id = i.data_space_id
ORDER BY rv.boundary_id DESC) rv
OUTER APPLY (SELECT ISNULL(pt.TypeName, rv.TypeName) AS TypeName,
ISNULL(pt.VALUE, rv.VALUE) AS PartitionValue,
ISNULL(pt.PartitionCnt, rv.PartitionCnt) AS PartitionCnt,
ISNULL(pt.boundary_value_on_right, rv.boundary_value_on_right) AS BoundaryOnRight
WHERE ds.TYPE = 'PS') p7
OUTER APPLY (SELECT CASE WHEN p7.TypeName LIKE '%char%' THEN '''' + REPLACE(CAST(PartitionValue AS VARCHAR), '''', '''''') + ''''
WHEN p7.TypeName LIKE '%date%'
OR p7.TypeName LIKE '%time%' THEN '''' + CAST(PartitionValue AS VARCHAR) + ''''
ELSE CAST(PartitionValue AS VARCHAR)
END AS CharValue,
CASE WHEN p7.TypeName LIKE '%char%' THEN '''' + REPLACE(CAST(pt.PrevValue AS VARCHAR), '''', '''''') + ''''
WHEN p7.TypeName LIKE '%date%'
OR p7.TypeName LIKE '%time%' THEN '''' + CAST(pt.PrevValue AS VARCHAR) + ''''
ELSE CAST(pt.PrevValue AS VARCHAR)
END AS PrevCharValue
WHERE ds.TYPE = 'PS') pt2
OUTER APPLY (SELECT CASE WHEN p7.BoundaryOnRight = 0
THEN CASE WHEN p.partition_number = p7.PartitionCnt THEN g.PartitionColumn + ' > ' + pt2.CharValue
WHEN p.partition_number > 1
THEN g.PartitionColumn + ' > ' + pt2.PrevCharValue + ' AND ' + g.PartitionColumn + ' <= ' + pt2.CharValue
ELSE g.PartitionColumn + ' <= ' + pt2.CharValue
END
WHEN p7.BoundaryOnRight = 1
THEN CASE WHEN p.partition_number = p7.PartitionCnt THEN g.PartitionColumn + ' >= ' + pt2.CharValue
WHEN p.partition_number > 1
THEN g.PartitionColumn + ' >= ' + pt2.PrevCharValue + ' AND ' + g.PartitionColumn + ' < ' + pt2.CharValue
ELSE g.PartitionColumn + ' < ' + pt2.CharValue
END
END AS PartitionFilter
WHERE ds.TYPE = 'PS') rf
WHERE o.is_ms_shipped = 0