SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Fun with sp_rename

Currently the only way to rename objects in SQL Server is to use sp_rename. Although that may be changing in the near future. There is a new command RENAME that is specifically for Azure SQL Data Warehouse and Parallel Data Warehouse. With any luck they will migrate that to Azure SQL Database and eventually SQL Server itself. Regardless, for now it’s sp_rename, which is useful, but it does have a few odd properties.

The first thing is that if you change the name of a stored procedure, function, etc you aren’t changing the name as it exists in the code. That can be a real pain, but that’s not where the fun part of renaming comes in.

Let’s take a look at the parameters:

  • @objname
  • @newname
  • @objtype

 
We are going to ignore the third parameter (yes, sp_rename has a third parameter). I’ve probably used it 2, maybe 3 times in all the time I’ve been using SQL. Working backwards (since we started at the end), the second parameter is the new name of the object being renamed, and the first is the existing name of the object. So where does that get weird?

The first parameter is (or at least can be) the fully qualified name of the object. So dbo.tablename or dbo.tablename.columnname. But the second column is only the name of the object. Let’s say that last bit one more time. The second column is only the name of the object. Why does that matter? Well, let’s try a simple example:

EXEC sp_rename 'dbo.tablename', 'dbo.tablename'

Anyone want to guess what the new name is going to be?

  • Schema: dbo
  • Object: dbo.tablename

 
Or written out it would look like this [dbo].[dbo.tablename]. Unfortunately, since SQL requires []’s (or “s) around a name with spaces, periods, or other special characters we can’t just call it like this: dbo.dbo.tablename. What that means is that in order to fix this (and yea, it happens every now and again by accident) we have to do this:

EXEC sp_rename '[dbo].[dbo.tablename]', 'tablename'

End result, it’s not really all that hard to get right. You just have to remember that while that the first parameter is a fully qualified name, the second should only be the actual new name. Well, unless you feel like messing with someone.

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...