Check all indexes in all table in Database in MS SQL Server

 USE YourDatabaseName; -- Replace with your actual database name

-- Query to retrieve index information
SELECT 
    TableName = t.name,
    IndexName = ind.name,
    IndexType = CASE ind.index_id 
                    WHEN 0 THEN 'Heap (No Clustered Index)'
                    WHEN 1 THEN 'Clustered'
                    ELSE 'Nonclustered'
                END,
    ColumnName = col.name,
    ColumnPosition = ic.key_ordinal
FROM 
    sys.indexes ind
INNER JOIN 
    sys.index_columns ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id
INNER JOIN 
    sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
INNER JOIN 
    sys.tables t ON ind.object_id = t.object_id
WHERE 
    ind.type_desc <> 'Heap' -- Exclude heap tables (tables without clustered indexes)
ORDER BY 
    t.name, ind.name, ic.key_ordinal;

Explanation:

1. USE YourDatabaseName: Replace YourDatabaseName with the name of your SQL Server database to switch to that database context.

2. sys.indexes: This system view contains information about indexes in the database.

3. sys.index_columns: This view provides details about the columns that are part of each index.

4. sys.columns: This view gives information about columns in tables.

5. sys.tables: This view contains information about tables in the database.

6. SELECT: Retrieves the table name (t.name), index name (ind.name), index type (ind.index_id), column name (col.name), and column position (ic.key_ordinal).

7. CASE statement: Checks the ind.index_id to determine the type of index (clustered, nonclustered, or heap).

8. WHERE: Filters out heap tables (ind.type_desc <> 'Heap') because they don't have traditional indexes.

9. ORDER BY: Orders the results by table name (t.name), index name (ind.name), and column position (ic.key_ordinal).

Notes:

• Make sure to replace YourDatabaseName with the actual name of your database.
• This query provides comprehensive information about all indexes in the database except for heap tables.
• Running this query requires appropriate permissions to access the system views (sys.indexes, sys.index_columns, sys.columns, sys.tables).

By executing this query in SQL Server Management Studio (SSMS) or any other SQL query tool, you can obtain a detailed list of indexes and their configurations across all tables in your database.