SQL Server - List all indexes of all tables

29 December,2010 by Jack Vamvas

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?


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment on SQL Server - List all indexes of all tables


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer