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