Rename badly named index

Question

Rename badly named index

DavidKBennett

Right there with Babe

Points: 752
More actions
May 29, 2012 at 6:23 am

#261843

This is an embarrassing one.
I was multitasking through three things, one of which was creating a new index on a large table. I had been using code from the missing index section of an execution plan so the code included the line:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
I had made the other required changes to have the index ready to go when I got a call and an IM simultaneously, and in the process of handling both of those I hit execute on the index creation code, forgetting that I hadn't yet put in a real index name.
I realized my error almost immediately, but couldn't stop the code. I figured I'd just rename it when it finished, except that sp_rename won't accept that name. Neither will virtually any of the other things I've tried.
Does anyone have an idea of how to rename this?
Thanks,
Dave

Viewing 13 posts - 1 through 12 (of 12 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

can you post the exact line of SQL you run to create your index?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

DavidKBennett Right there with Babe Points: 752 More actions · Answer 2

Not sure this helps but, there is the full code:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[srvrpf] ([AONROV],[AORCCD],[AOSTA9],[AOCEK6],[AOSVCD])

INCLUDE ([AOCMPY],[AODIVN],[AOFMAJ])

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Drop the index and recreate it.

Edit, forgot there was an sp_rename for indexes. That's probably better.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

david.bennett2 (5/29/2012)
Not sure this helps but, there is the full code:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[srvrpf] ([AONROV],[AORCCD],[AOSTA9],[AOCEK6],[AOSVCD])
INCLUDE ([AOCMPY],[AODIVN],[AOFMAJ])

This: -

EXEC sp_rename N'[srvrpf].[<Name of Missing Index, sysname,>]', N'WHATEVER_NAME_YOUR_WANT_HERE', N'INDEX';

Or this: -

IF EXISTS (SELECT 1

FROM sys.indexes

WHERE object_id = OBJECT_ID(N'[dbo].[srvrpf]') AND NAME = N'[<Name of Missing Index, sysname,>]'

)

BEGIN

DROP INDEX [<Name of Missing Index, sysname,>] ON [dbo].[srvrpf];

END;

CREATE NONCLUSTERED INDEX NEW_NAME_HERE ON [dbo].[srvrpf] ([AONROV], [AORCCD], [AOSTA9], [AOCEK6], [AOSVCD]

) INCLUDE ([AOCMPY], [AODIVN], [AOFMAJ]);

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

DavidKBennett Right there with Babe Points: 752 More actions · Answer 5

OK, so the rename still was a no go, but with some more tweaking I did get the drop to work. Now I've just got to wait for the index to be built again.

Thanks All,

Dave

ScottPletcher SSC Guru Points: 101126 More actions · Answer 6

You can rename an index through SSMS; just backspace over the old name, and type in the new one.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

DavidKBennett Right there with Babe Points: 752 More actions · Answer 7

That was the first thing I tried, but when you have an index as badly named as that one, it doesn't work. I'm still not certain how it even allowed that name in the first place.

ScottPletcher SSC Guru Points: 101126 More actions · Answer 8

What do you mean "doesn't work"? It won't even initiate the rename process?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101126 More actions · Answer 9

On my SQL 2005 version and my SQL 2008 versions, it accepts sp_rename for that index name:

USE tempdb

CREATE TABLE dbo.nasty_index_name_test ( c1 int )

GO

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[nasty_index_name_test] (c1)

GO

EXEC sp_helpindex 'dbo.nasty_index_name_test'

GO

EXEC sp_rename 'nasty_index_name_test.<Name of Missing Index, sysname,>', 'index1'

GO

EXEC sp_helpindex 'dbo.nasty_index_name_test'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

DavidKBennett Right there with Babe Points: 752 More actions · Answer 10

It would give an error, I don't have the exact wording, but it was along the lines of "The item doesn't exist or is not of type index."

DavidKBennett Right there with Babe Points: 752 More actions · Answer 11

I just tried the same test you did, and got similar results. This is not what I saw with the index I had created the started this thread. Something else was going on, but I'm not certain what.

Thanks for the feedback,

Dave

ScottPletcher SSC Guru Points: 101126 More actions · Answer 12

Interesting. Thanks for the update.

A little confusing though, not sure what's going on there then. Thought originally it might have just been a mismatched name because of the unusual way the names have to be specified in sp_rename :-).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".