Use below query to get Index Name, Key Columns, Included Columns, and Filter (WHERE clause) columns for filtered indexes:
Scenario, if dev tea m will complaint about the index not deployed or if we want know which columns are i key columns and which columns are included columns. In this case below will help us to get the those details.
SELECT
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
i.name AS IndexName,
i.type_desc,
i.is_unique,
i.has_filter,
i.filter_definition AS WhereClause,
KeyColumns =
STUFF((
SELECT ', ' + c.name
FROM sys.index_columns ic
JOIN sys.columns c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'),1,2,''),
IncludedColumns =
STUFF((
SELECT ', ' + c.name
FROM sys.index_columns ic
JOIN sys.columns c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 1
ORDER BY c.name
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'),1,2,'')
FROM sys.indexes i
JOIN sys.tables t
ON i.object_id = t.object_id
WHERE i.index_id > 0
AND i.name IN ('ix_uq_contract_jtt_component_contract_jtt_id_component_master_id','ix_uq_customer_bundle_component')
ORDER BY SchemaName, TableName, IndexName
