Create index via SQL stored procedure
Posted on 2010-11-16
I am attempting to create a stored procedure which will generate indexes on SQL tables, if they do not already exist
Code is below.
However, I keep getting the error:
Msg 1913, Level 16, State 1, Line 1
The operation failed because an index or statistics with name 'IX_d_StudentsCurrentShortCS_studentNumber' already exists on table 'REAdw.dbo.d_StudentsCurrentShortCS'.
I know that I cannot create an index if the index name alreay exists in sys.indexex, but I am using code to check for it and delete if already there, before creating/re-creating.
Any guidance would be most appreciated
- - - - -
/****** Object: StoredProcedure [dbo].[spd_cube_transcript] Script Date: 11/16/2010 10:23:43 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
alter proc spd_MakeIndexes
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_d_StudentsCurrentShortCS_studentNumber')
DROP INDEX IX_d_StudentsCurrentShortCS_studentNumber ON REAdw.dbo.d_StudentsCurrentShortCS;
CREATE INDEX IX_d_StudentsCurrentShortCS_studentNumber
ON REAdw.dbo.d_StudentsCurrentShortCS (studentNumber);