Listing All Indexes in Database in SQL Server

Hello everyone,

In this article, I will give information about listing all the indexes in the database in SQL Server.

In SQL Server, in some cases, we may want all index values in the database to be listed.

You can easily do this with the help of the code below.

SELECT i.[name] AS index_name,
       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS [columns],
           WHEN i.[type] = 1 THEN
               'Clustered index'
           WHEN i.[type] = 2 THEN
               'Nonclustered unique index'
           WHEN i.[type] = 3 THEN
               'XML index'
           WHEN i.[type] = 4 THEN
               'Spatial index'
           WHEN i.[type] = 5 THEN
               'Clustered columnstore index'
           WHEN i.[type] = 6 THEN
               'Nonclustered columnstore index'
           WHEN i.[type] = 7 THEN
               'Nonclustered hash index'
       END AS index_type,
           WHEN i.is_unique = 1 THEN
               'Not unique'
       END AS [unique],
       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS table_view,
           WHEN t.[type] = 'U' THEN
           WHEN t.[type] = 'V' THEN
       END AS [object_type]
FROM sys.objects t
    INNER JOIN sys.indexes i
        ON t.object_id = i.object_id
    SELECT col.[name] + ', '
    FROM sys.index_columns ic
        INNER JOIN sys.columns col
            ON ic.object_id = col.object_id
               AND ic.column_id = col.column_id
    WHERE ic.object_id = t.object_id
          AND ic.index_id = i.index_id
    ORDER BY col.column_id
    FOR XML PATH('')
) D(column_names)
WHERE t.is_ms_shipped <> 1
      AND index_id > 0
ORDER BY i.[name];

When you run the code, you will get a result like the one below.

Listing All Indexes in Database in SQL Server

As you can see, all index values are listed.

I ran the transaction on the Northwind database in order to be an example transaction. You can run it on the database you want.

Good luck to everyone in business and life.


Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!