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
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
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?
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |