T-SQL Tutorial

SQL Check if index exists on column


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:

IDNAMEDURATIONPRICE
1SQL tutorial2200
2T-SQL tutorial5300
3Learn ASP5400
4PHP tutorial3200
5Learn HTML2100

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