How to List all indexes of all SQL tables

29 December,2010 by Tom Collins

If you require a list of every SQL index on every table on a SQL Server database use  the query below



  T.[name] AS [table_name], I.[name] AS [index_name]

FROM sys.[tables] AS T   

  INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]   

 WHERE  I.[type_desc] <> 'HEAP' 

ORDER BY T.[name], I.[index_id]


A few details of the query , which will allow customising – depending on requirements.

 The “type_desc” column -  gives a description of the index type. In SQL Server 2005 – possible types are: Heap, Clustered,NonClustered,XML

As a table is either a clustered table or a heap – I’m not interested in heaps – for the purpose of this query

Read More

Top 5 SQL Server DMV for Index Analysis - SQL Server DBA

How to decide on Index - ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS

SQL Server non-clustered index checklist - SQL Server DBA

SQL Server - Why does Query Optimizer choose a Clustered Index Scan?

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on How to List all indexes of all SQL tables | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer