/*
	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