How can we verify that a table has an index or not?

Question

How can we verify that a table has an index or not? if there is, how to find this index for a specific column for a table?

Regards, Kumar

Answer 1

In SQL Server Management Studio, you can navigate the tree to a table of interest to you and open node indexes. Double-clicking on any index in a node will open a properties dialog box that shows which columns are included in the index.

If you want to use T-SQL, this may help:

SELECT sys.tables.name, sys.indexes.name, sys.columns.name FROM sys.indexes INNER JOIN sys.tables ON sys.tables.object_id = sys.indexes.object_id INNER JOIN sys.index_columns ON sys.index_columns.index_id = sys.indexes.index_id AND sys.index_columns.object_id = sys.tables.object_id INNER JOIN sys.columns ON sys.columns.column_id = sys.index_columns.column_id AND sys.columns.object_id = sys.tables.object_id WHERE sys.tables.name = 'TABLE NAME HERE' ORDER BY sys.tables.name, sys.indexes.name, sys.columns.name 

Answer 2

Try

 select object_name(object_id),* from sys.indexes where object_name(object_id) = 'your table name' 

Answer 3

ordering by column name is incorrect, you need to order by position in the index, so order by clause should be tabname, indname and sys.index_columns.index_column_id ...