usp_SQLServerCarpenter_Tools_Estimate_Nonclustered_Indexes_Clashing_With_Clustered_Index

/*
Author:Brahmanand Shukla (SQLServerCarpenter.com)
Date:27-May-2022
Purpose:This procedure will list down all the nonclustered indexes, which includes key columns as that of the clustered index. 
It will also list the key columns of both – nonclustered index, and the corresponding clustered index, 
along with the key ordinal of the clustered index key columns in the respective nonclustered index.
It is now up to you to decide which one makes sense, and which doesn’t. 
Take the decision accordingly and delete the ones that do not make sense.  
This procedure may be helpful in performance tuning assignments.
Example: 
EXEC usp_SQLServerCarpenter_Tools_Estimate_Nonclustered_Indexes_Clashing_With_Clustered_Index
*/CREATE OR ALTER PROCEDURE usp_SQLServerCarpenter_Tools_Estimate_Nonclustered_Indexes_Clashing_With_Clustered_Index
AS
BEGIN
SET NOCOUNT ON;
; WITH cte_clustered_index
AS
(
SELECT C.name AS TableName
, A.name AS IndexName
, D.name AS ColumnName
, B.key_ordinal
FROM sys.indexes A
INNER JOIN sys.index_columns B
ON B.object_id = A.object_id
AND B.index_id = A.index_id
INNER JOIN sys.tables C
ON C.object_id = A.object_id
INNER JOIN sys.columns D
ON D.object_id = B.object_id
AND D.column_id = B.column_id
WHERE A.type_desc = 'CLUSTERED'
AND A.is_hypothetical = 0
)
, cte_nonclustered_index
AS
(
SELECT C.name AS TableName
, A.name AS IndexName
, D.name AS ColumnName
, B.key_ordinal
FROM sys.indexes A
INNER JOIN sys.index_columns B
ON B.object_id = A.object_id
AND B.index_id = A.index_id
INNER JOIN sys.tables C
ON C.object_id = A.object_id
INNER JOIN sys.columns D
ON D.object_id = B.object_id
AND D.column_id = B.column_id
WHERE A.type_desc = 'NONCLUSTERED'
AND A.is_hypothetical = 0
AND B.is_included_column = 0
)
SELECT CL.TableName
, CL.IndexName AS Clustered_Index_Name
, NCL.IndexName AS Nonclustered_Index_Name
, STUFF
(
(
SELECT ', ' + ColumnName 
FROM cte_clustered_index TMP
WHERE TMP.TableName = CL.TableName
AND TMP.IndexName = CL.IndexName
ORDER BY TMP.key_ordinal ASC
FOR XML PATH ('')
), 1, 2, ''
)AS Clustered_Index_Key
, STUFF
(
(
SELECT ', ' + ColumnName 
FROM cte_nonclustered_index TMP
WHERE TMP.TableName = NCL.TableName
AND TMP.IndexName = NCL.IndexName
ORDER BY TMP.key_ordinal ASC
FOR XML PATH ('')
), 1, 2, ''
)AS Nonclustered_Index_Key
, STUFF
(
(
SELECT ', ' + CAST(TMP.key_ordinal AS VARCHAR)
FROM cte_nonclustered_index TMP
WHERE TMP.TableName = CL.TableName
AND TMP.ColumnName = CL.ColumnName
ORDER BY TMP.key_ordinal ASC
FOR XML PATH ('')
), 1, 2, ''
)AS Key_Ordinal_Of_Clustered_Index_Key_Columns_In_Nonclustered_Index_Key
FROM cte_clustered_index CL
INNER JOIN cte_nonclustered_index NCL
ON CL.TableName = NCL.TableName
WHERE EXISTS (SELECT 1 FROM cte_clustered_index CL WHERE CL.TableName = NCL.TableName AND CL.ColumnName = NCL.ColumnName)
END