How can we check that table have index or not ?

Question

Hi,

How can we check that table have index or not ? if have how to find that index for a particular column for a table?

Regards, kumar

Answer 1

In SQL Server Management Studio you can navigate down the tree to the table you're interested in and open the indexes node. Double clicking any index in that node will then open the properties dialog which will show which columns are included in the index.

If you would like to use T-SQL, this might 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 wrong, you need to order by the position in the index, so order by clause should be tabname, indname and sys.index_columns.index_column_id...