sp_rename throwing errors when trying to rename index

Question

Post reply

sp_rename throwing errors when trying to rename index

brian_winzeler

Mr or Mrs. 500

Points: 564
More actions
October 21, 2010 at 6:57 pm

#143197

I'm currently building a script to rename a number of my indexes but I'm running into problems with some of them. Occasionally it will spit out an error as follows:
Msg 15225, Level 11, State 1, Procedure sp_rename, Line 332
No item by the name of 'ix_name' could be found in the current database 'db', given that @itemtype was input as '(null)'.
The object is in the dbo schema so that's not the problem. What else might cause this to happen?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Can you please post:

1. the line that is failing in your script.

2. the table and index CREATE statements when you script them out.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

brian_winzeler (10/21/2010)
Msg 15225, Level 11, State 1, Procedure sp_rename, Line 332
No item by the name of 'ix_name' could be found in the current database 'db', given that @itemtype was input as '(null)'.

See if you can find entry of your index in following query

USE yourdb

GO

select * from sys.indexes where name = 'ix_name'

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

brian_winzeler (10/21/2010)
Msg 15225, Level 11, State 1, Procedure sp_rename, Line 332
No item by the name of 'ix_name' could be found in the current database 'db', given that @itemtype was input as '(null)'.

Try this syntax:

sp_rename 'TableName.IndexName', 'NewIndexName'

brian_winzeler Mr or Mrs. 500 Points: 564 More actions · Answer 4

That worked Suresh. Thanks! As usual it's something simple that escaped me :P.