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.
