CREATE VIEW Metadata.IndexPartitionDetails
AS
SELECT s.name AS SchemaName,
o.name ObjectName,
o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AS ObjectType,
ix.name AS IndexName,
ix.index_id AS IndexId,
ix.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
ds.type AS DataspaceType,
ds.name AS DataspaceName,
ix.is_unique AS IsUnique,
ix.is_primary_key AS IsPrimaryKey,
ix.IGNORE_DUP_KEY AS IgnoreDupKey,
ix.is_unique_constraint AS IsUniqueConstraint,
ix.is_disabled AS IsDisabled,
ix.has_filter AS HasFilter,
pr.partition_number AS PartitionNumber,
pr.DATA_COMPRESSION AS DataCompression,
pr.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS DataCompressionDesc,
pr.ROWS,
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 AS ix (NOLOCK) ON o.OBJECT_ID = ix.OBJECT_ID
INNER JOIN sys.data_spaces AS ds (NOLOCK) ON ds.data_space_id = ix.data_space_id
INNER JOIN sys.partitions AS pr (NOLOCK) ON pr.OBJECT_ID = ix.OBJECT_ID
AND pr.index_id = ix.index_id
WHERE o.is_ms_shipped = 0
AND o.type IN ('U','V')