How to identify duplicate indexes along with columns in SQL Server?

To get the key column list from indexes that are duplicates in SQL Server, you can use the following query:

use databasename
go

WITH DuplicateIndexes AS (
    SELECT 
        i.OBJECT_ID,
        i.index_id
    FROM 
        sys.index_columns ic
    JOIN 
        sys.indexes i ON i.OBJECT_ID = ic.OBJECT_ID 
                     AND i.index_id = ic.index_id
    WHERE 
        i.type_desc <> 'HEAP' 
		AND OBJECT_NAME(i.OBJECT_ID) NOT LIKE '%sys%' --excluding system tables
    GROUP BY 
        i.OBJECT_ID, i.index_id
    HAVING 
        COUNT(*) > 1 -- to check duplicates 
)

SELECT 
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    OBJECT_NAME(ic.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS IndexedColumns
FROM 
    sys.index_columns ic
JOIN 
    sys.indexes i ON i.OBJECT_ID = ic.OBJECT_ID 
                 AND i.index_id = ic.index_id
JOIN 
    sys.objects o ON o.OBJECT_ID = ic.OBJECT_ID
JOIN 
    sys.columns c ON ic.OBJECT_ID = c.OBJECT_ID 
                 AND ic.column_id = c.column_id
JOIN 
    DuplicateIndexes di ON di.OBJECT_ID = ic.OBJECT_ID 
                        AND di.index_id = ic.index_id
GROUP BY 
    o.schema_id, ic.OBJECT_ID, i.name;
  

This query first identifies the indexes that are duplicates, and then retrieves the table name, index name, and the key column list for each duplicate index.

Execute this query in your SQL Server management tool to get the key column list from indexes that are duplicates in your database.

Removing Cached login and password list in SQL Server Management Studio

You need to look in following location based on the SSMS Instance you have in your local PC.

Since mine is 19.0 version, Below is my path.

C:\Users\sconrey\AppData\Roaming\Microsoft\SQL Server Management Studio\19.0

Open UserSettings.xml in Notepad ++ or any editor of your choice.

Find the User you would like to remove and delete the Entire Element tag related to that User.

<ServerTypeItem>
    <Servers>   
        <Element>
 

        </Element>
    </Servers>
< /ServerTypeItem>

You need to remove complete Element tag from the file and save it. Please make sure during this process. SSMS should be closed, if not your changes will not eb updated.

SSMS: Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)

We will see this when we connect to SQL Server 2012 from SQL Server 2008/2008 R2

Cause
This issue occurs because of an error in SSMS 2008 R2.

Solution
The problem is generated due to an error in SQL Server Management Studio 2008 & 2008 R2.

Fix
To solve this, apply the latest service pack available.