Viewing Table Based Index Information in SQL Server

Hello everybody,

In this article, I will try to give information about displaying table-based Index information in SQL Server.

In SQL Server, in some cases, you may want to display table-based Index information.

You can easily do this using the code below.

SELECT sys.objects.object_id,
       sys.objects.name AS tablename,
       sys.indexes.name AS indexname,
       sys.columns.name AS columnname
FROM sys.objects
    INNER JOIN sys.indexes
        ON sys.objects.object_id = sys.indexes.object_id
    INNER JOIN sys.index_columns
        ON sys.index_columns.object_id = sys.indexes.object_id
           AND sys.index_columns.index_id = sys.indexes.index_id
    INNER JOIN sys.columns
        ON sys.columns.object_id = sys.index_columns.object_id
           AND sys.columns.column_id = sys.index_columns.column_id
WHERE sys.objects.type_desc = 'USER_TABLE'
      AND sys.indexes.is_primary_key = 0
ORDER BY tablename,
         indexname,
         columnname;

When you create and run the above code, you will see a result similar to the one below.

As you can see, we have displayed the table-based Index information.

Good luck to everyone in business and life.