Before we get started…
I know. I know. BOL. It’s documented.
They even show you how to rename a table.
But sometimes…
You just forget.
And as with most simple mistakes, fixing them is… Weird.
Here’s what happened to me recently, when I was working on a table swapping demo.
|
1 2 3 4 |
CREATE TABLE dbo.Whatever (i int) SELECT * FROM dbo.[Whatever] AS [w] |
Here’s where I was going to rename it, and then make another table with the INT column as a BIGINT.
|
1 2 3 |
EXEC [sys].[sp_rename] @objname = N'dbo.Whatever' , @newname = N'dbo.Whatever_New' |
Which worked, except…
dbo.dbo.
Like most people who make mistakes, I decided to revisit the documentation afterwards. And, yeah, you don’t specify schema in the new object name.
So, now that you all know you’re smarter than me, how would you fix it?
I’ll spare you the trial and error:
|
1 2 3 |
EXEC [sys].[sp_rename] @objname = N'dbo.[dbo.Whatever_New]' , @newname = N'Whatever_New' |
There were quite a few different arrangements of brackets and schema prefixes leading up to this.
I hope this post saves someone a little time.
Brent says: dear reader, please use this as part of an April Fool’s prank. Just not in production.
28 Comments. Leave new
Try this on SQL 2008 and 2008 R2… Create a view, then rename it. Then create a view with the old name of the first view but with a diferent query. Then run sp_refreshview on the the two views. Then check the deffinition of the the views.
http://sqlblog.com/blogs/davide_mauri/archive/2010/10/12/bug-with-sp-rename-sp-refreshview.aspx
Ha, I remember this “dbo.dbo.” causing an error in our testing environment years ago and spending a fair amount of time trying to untangle what was going on and how to fix it. Thanks for a post that brings back amusing memories!
LOL noob
😉
It makes sense that you don’t specify the schema in the @newname. Otherwise it would imply that you could change the schema with this method.
While I will admit that I am the “DBA by Accident” type, I am a little confused why this sproc exists…clearly you used to the GUI to demonstrate the “gotcha”. So, why wouldn’t you just right-click on the object and select Rename??? I read M$ documentation, and I don’t see any special functionality that derives from this sproc method over the GUI…Am I missing something?
Because scripts.
For everything you can manipulate in the GUI, there should be an equivalent in TSQL. So you can automate / script it.
Are you going to do that on your Development, Test and Production servers?
What happens if you accidentally type it incorrectly or hit ‘enter’ before you wanted to in one of the environments
What happens if this takes down your business critical website?
I would reverse the question. Why would you do something in the GUI that you can do in a script? 🙂
I did the same thing a while back only I managed to put []’s around my table name. So the actual table name was “[tablename]”. Took me a while to figure out what I’d done since all of my errors came back looking like:
[tablename] does not exist
Which looked ok to me.
Oh, those are wonderful. When the software is suddenly overly literal rather than context sensitive.
HAHAHAHA Laughing with you! Not at you! 😀
I’m chuckling, but only because I did this about a month ago and figured out what happened by looking at (you guessed it) BOL. 🙂
I just had this for a user yesterday actually.
This kind of stuff drives me mental just through not being predictable. I get so fed up with having to check the syntax every time I need a statement. I tend to remember the concept regarding what the function achieves.. but then have to look up .. is a table valued function or just a view… which parameters are mandatory … does the WITH clause need brackets or not… sigh. It will never change.
Not sure what happened to my first post: what is the value of using this SP over the GUI? I earned the “DBA by Accident” role, so if there is something I am missing, educate me.
The advantage of using the SP over the GUI is having the change scripted out so that the change you are making in dev today you can just run in test tomorrow and prod next week. You might also only be able to make changes to prod by checking them into source control and getting somebody else to make them.
Ahhh, the fun parts that I miss out on because, not only do we TECHNICALLY not have team managing our servers, we don’t have a difference between Dev/Test/Prod…not going to defend our environment; just suffice it to say, I have a lot of control here…
On top of what Stuart mentions, the GUI doesn’t always work either. It didn’t in the real life scenario the demo is based on. If anything else is accessing the object, you’ll just get a lock timeout error. I’ve seen Intellisense be part of a blocking issue at times. It probably would have worked for the demo case, though, since it was on my laptop.
Also, sometimes you want to do several renames in one transaction while locking the object(s).
There are a couple of other “oops that hurt” little stumbling blocks.
For example Renaming the table does not rename the other dependent objects such as Triggers, Constraints, and Keys. These things exist independently in Sys.Objects and so must have unique names.
I used sp_rename for the first time not long ago. I used the example from the documentation so got t right but will have to remember this so I don’t get it wrong next time
or you could just code around it:
SELECT
Pizza [Pizza]
FROM
dbo.[dbo.Pan] Pan
well in my corp often peaople rename _bak _OLD _NEWX ect beacause is not use source control all time..so hopfully is not on production but somtime when need do shema compare is verry weird i take more time clean garbage.
Also – Don’t forget that sp_rename does not rename the other objects associated with a table, like the Primary Key, Constraints, Indexes, Triggers, etc. When you rename a table, you likely also need to rename all of those other objects as well.
It is really annoying, funnily enough the ssms object explorer thing lets you rename it back without having to think about [‘s in the right places.
Groooooaaannn…. Yep, done that.
I like the feature that instantaneously drops all indexes on an index view after doing an ALTER VIEW, without warning. Even when nothing was changed. This has made me laugh a few times, then cry.
“Feature”
agreed. considering all the paperwork and hoop-jumping it can take to get an index built on a view in the first place, the fact that it can all evaporate with a simple ALTER VIEW is a darned refreshing bit of convenience, i say.
Another issue is if you do a rename and have code that follows which uses the renamed table. Validation checks fail:
EXEC sp_rename ‘dbo.table1’ ‘table2’
SELECT * FROM dbo.table2…
Gives “Invalid object name ‘table2′”. Hang on… didn’t I just rename it? It seems the validator (at least on old SQL Versions), fails to acknowledge the rename.