Solved

Create index via SQL stored procedure

Posted on 2010-11-16
3
546 Views
Last Modified: 2012-06-21
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

- - - - -
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_StudentsCurrentShortCS_studentNumber')
    DROP INDEX IX_d_StudentsCurrentShortCS_studentNumber ON REAdw.dbo.d_StudentsCurrentShortCS;
GO
CREATE INDEX IX_d_StudentsCurrentShortCS_studentNumber
    ON REAdw.dbo.d_StudentsCurrentShortCS (studentNumber);
GO
0
Comment
Question by:JEClark1
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 34147032
You are creating the index when you create the stored procedure. GO denotes the end of the stored procedure and the subsequent command is then processed on its own.

Instead do the following.

alter proc spd_MakeIndexes
as
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_d_StudentsCurrentShortCS_studentNumber')
BEGIN TRAN
DROP INDEX IX_d_StudentsCurrentShortCS_studentNumber ON REAdw.dbo.d_StudentsCurrentShortCS;
COMMIT TRAN
BEGIN TRAN
CREATE INDEX IX_d_StudentsCurrentShortCS_studentNumber ON REAdw.dbo.d_StudentsCurrentShortCS (studentNumber); 
COMMIT TRAN
GO 

Open in new window

0
 

Author Closing Comment

by:JEClark1
ID: 34147122
Thank you for a quick and concise response!  Worked perfectly.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34148065
Glad to be of assistance.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
replication - alerts? 4 32
SQL Transaction logs 8 29
Using CTE to insert records into a table 2 30
MS SQL Inner Join - Multiple Join Parameters 2 24
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now