Monday 15 February 2016

MSSQL - Query to list all duplicated indexes


watch this example on YouTube:



;With DupInx As (Select SCH.name as SchemaName, OBJ.name as TableName,
IDX.name as IndexName,
INDEX_COL(SCH.name + '.' + OBJ.Name, IDX.index_Id, 1) as Col1,
INDEX_COL(SCH.name + '.' + OBJ.Name, IDX.index_Id, 2) as Col2,
INDEX_COL(SCH.name + '.' + OBJ.Name, IDX.index_Id, 3) as Col3,
INDEX_COL(SCH.name + '.' + OBJ.Name, IDX.index_Id, 4) as Col4,
INDEX_COL(SCH.name + '.' + OBJ.Name, IDX.index_Id, 5) as Col5,
INDEX_COL(SCH.name + '.' + OBJ.Name, IDX.index_Id, 6) as Col6
From sys.indexes as IDX INNER JOIN sys.objects as OBJ on IDX.object_id = OBJ.object_id INNER JOIN
 sys.schemas as SCH on SCH.schema_id = OBJ.schema_id
Where (IDX.index_id>0))
Select MD1.SchemaName, MD1.TableName, MD1.IndexName, MD2.IndexName As DuplicatedIndex, MD1.Col1, MD1.Col2, MD1.Col3
From DupInx as MD1 INNER JOIN DupInx as MD2 on MD1.TableName = MD2.TableName and
MD1.IndexName <> MD2.IndexName
and isnull(MD1.Col1, '') = isnull(MD2.Col1, '') 
and isnull(MD1.Col2, '') = isnull(MD2.Col2, '') 
and isnull(MD1.Col3, '') = isnull(MD2.Col3, '') 
and isnull(MD1.Col4, '') = isnull(MD2.Col4, '') 
and isnull(MD1.Col5, '') = isnull(MD2.Col5, '') 

1 comment: