Listing Indexes Linked to Tables in SQL Server

Hello to everyone,

In this article, I will try to give information about listing indexes linked to tables in SQL Server.

In SQL Server, in some cases, you may want to list the indexes that are linked to the tables.

You can easily do this using the code below.

SELECT QUOTENAME(t.name) AS [Table Name],
       QUOTENAME(i.name) AS [Index Name],
       i.is_primary_key AS [Is Auto-Incrementing],
       STUFF(REPLACE(REPLACE(
                     (
                         SELECT QUOTENAME(c.name) + CASE
                                                        WHEN ic.is_descending_key = 1 THEN
                                                            ' DESC'
                                                        ELSE
                                                            ''
                                                    END AS [data()]
                         FROM sys.index_columns AS ic
                             INNER JOIN sys.columns AS c
                                 ON ic.object_id = c.object_id
                                    AND ic.column_id = c.column_id
                         WHERE ic.object_id = i.object_id
                               AND ic.index_id = i.index_id
                               AND ic.is_included_column = 0
                         ORDER BY ic.key_ordinal
                         FOR XML PATH
                     ),
                     '<row>',
                     ', '
                            ),
                     '</row>',
                     ''
                    ),
             1,
             2,
             ''
            ) AS [Index Field Information],
       STUFF(REPLACE(REPLACE(
                     (
                         SELECT QUOTENAME(c.name) AS [data()]
                         FROM sys.index_columns AS ic
                             INNER JOIN sys.columns AS c
                                 ON ic.object_id = c.object_id
                                    AND ic.column_id = c.column_id
                         WHERE ic.object_id = i.object_id
                               AND ic.index_id = i.index_id
                               AND ic.is_included_column = 1
                         ORDER BY ic.index_column_id
                         FOR XML PATH
                     ),
                     '<row>',
                     ', '
                            ),
                     '</row>',
                     ''
                    ),
             1,
             2,
             ''
            ) AS [Index Include Information]
FROM sys.tables AS t
    INNER JOIN sys.indexes AS i
        ON t.object_id = i.object_id
    LEFT JOIN sys.dm_db_index_usage_stats AS u
        ON i.object_id = u.object_id
           AND i.index_id = u.index_id
WHERE t.is_ms_shipped = 0
      AND t.name LIKE '%'
      AND i.type <> 0
ORDER BY i.name;

When you run the above code block, you will see the following result.

As you can see, the indexes linked to the tables are listed.

Good luck to everyone in business and life.