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
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!