How to List all indexes of all SQL tables

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

SELECT 

  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?