JEClark1
asked on
Create index via SQL stored procedure
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_StudentsCurrentShort CS_student Number' already exists on table 'REAdw.dbo.d_StudentsCurre ntShortCS' .
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
- - - - -
USE [REAdw]
GO
/****** Object: StoredProcedure [dbo].[spd_cube_transcript ] Script Date: 11/16/2010 10:23:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter proc spd_MakeIndexes
as
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_d_StudentsCurrentShor tCS_studen tNumber')
DROP INDEX IX_d_StudentsCurrentShortC S_studentN umber ON REAdw.dbo.d_StudentsCurren tShortCS;
GO
CREATE INDEX IX_d_StudentsCurrentShortC S_studentN umber
ON REAdw.dbo.d_StudentsCurren tShortCS (studentNumber);
GO
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_StudentsCurrentShort
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
- - - - -
USE [REAdw]
GO
/****** Object: StoredProcedure [dbo].[spd_cube_transcript
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter proc spd_MakeIndexes
as
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_d_StudentsCurrentShor
DROP INDEX IX_d_StudentsCurrentShortC
GO
CREATE INDEX IX_d_StudentsCurrentShortC
ON REAdw.dbo.d_StudentsCurren
GO
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Glad to be of assistance.
ASKER