Check if index exists on column
To check if index exists on column uses sp_helpindex function or extract information from sys.tables, sys.columns, sys.indexes, sys.index_columns.
The function sp_helpindex return information about the indexes on a given table name.
Example
Tutorials table:
| ID | NAME | DURATION | PRICE |
|---|---|---|---|
| 1 | SQL tutorial | 2 | 200 |
| 2 | T-SQL tutorial | 5 | 300 |
| 3 | Learn ASP | 5 | 400 |
| 4 | PHP tutorial | 3 | 200 |
| 5 | Learn HTML | 2 | 100 |
Create index
CREATE INDEX tut_idx ON tutorials (id);
SQL select query
select
t.name table_name, t.object_id,
c.name column_name,c.column_id,
i.name index_name, i.type, i.type_desc
from
sys.tables t, sys.columns c,
sys.indexes i, sys.index_columns ic
where
t.object_id=c.object_id
and c.object_id=i.object_id
and ic.index_id=i.index_id
and ic.column_id=c.column_id
and t.name='tutorials';
Uses sp_helpindex function
USE model;
GO
EXEC sp_helpindex N'tutorials';
GO
