SQL Server – List Indexes For Database using T-SQL

SQL – Indexes. Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

Below is a script to determine the indexes for a specific table in a database. The results will also show you whether it is a CLUSTERED or NONCLUSTERED index.

SELECT
so.name AS TableName,
col.name AS ColumnName,
si.name AS IndexName,
si.type_desc AS IndexType
FROM
sys.indexes si
JOIN sys.objects so ON si.[object_id] = so.[object_id]
INNER JOIN
sys.index_columns ic ON  si.object_id = ic.object_id and si.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
WHERE
so.type = 'U'    --Only get indexes for User Created Tables
AND si.name IS NOT NULL
and so.name='users' --Change the table name to get the index list for a particular Table.
ORDER BY
so.name, si.type

Output:

SQL Server - List Indexes For Database using T-SQL


<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.