How do I check if a nonclustered index exists in SQL Server 2005

sql-server-2005 non-clustered-index
user532104 picture user532104 · May 3, 2011 · Viewed 62.7k times · Source

I have the following:

CREATE NONCLUSTERED INDEX [MyTableIndex]
ON [dbo].[tablename] ([tablename_ID],[tablename_Field1])
INCLUDE ([Tablename_Field2],[Tablename_Field3])

I want to create an if statement to check if this exists. How do I do this?

Answer

AdaTheDev picture AdaTheDev · May 3, 2011 
IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = 'MyTableIndex' AND object_id = OBJECT_ID('tablename'))
    BEGIN
        -- Index with this name, on this table does NOT exist
    END

Related questions

How to drop a unique constraint from table column?

I have a table 'users' with 'login' column defined as: [login] VARCHAR(50) UNIQUE NOT NULL Now I want to remove this unique constraint/index using SQL script. I found its name UQ_users_7D78A4E7 in my local database …

Read More
How to resolve 900 key length limit index on the column which have datatype varchar(4096) in SQL Server 2005?

This is the query for creating index create index idx_ncl_2 on BFPRODATTRASSOCIATION (value,attributeid) include (productid) Table structure of BFPRODATTRASSOCIATION ProdAttrAssociationId bigint no 8 ProductId bigint no 8 AttributeId bigint no 8 Value varchar no 4096 I am getting this error: The maximum …

Read More
Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More