January 13, 2017 at 8:53 am
I have a large table with huge number of filtered indexes. I need to find out duplicates. From what sys. view I can get filters?
Thanks
January 13, 2017 at 10:27 am
It's available in SYS.indexes as the filter_definition column.
this is a query I use to quickly see all the indexes on a table or list of tables:
for some reason I couldn't copy and paste script directly into forumn 🙁
January 13, 2017 at 12:46 pm
Chris Harshman - Friday, January 13, 2017 10:27 AMIt's available in SYS.indexes as the filter_definition column.
this is a query I use to quickly see all the indexes on a table or list of tables:
for some reason I couldn't copy and paste script directly into forumn 🙁
Ah, thank! I may have overlooked it
January 13, 2017 at 1:09 pm
SELECT s.name + N'.' + t.name AS table_name, i.name AS index_name, i.type_desc, i.is_unique, i.is_primary_key,
SubString(
(SELECT N', ' + c.name + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N'' END
FROM sys.index_columns ic INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.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('')), 3, 1000) AS columns,
SubString(
(SELECT N', ' + c.name
FROM sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
ORDER BY ic.index_column_id
FOR XML PATH('')), 3, 1000) AS included,
i.filter_definition
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s on t.schema_id = s.schema_id
WHERE i.name IS NOT NULL
AND i.object_id > 100
AND t.name in ('AgentStatus')
ORDER BY s.name, t.name, i.name
got it to paste now :crazy:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply