SP_RENAME

What is SP_RENAME?

If you have objects in your database that you want to rename, you can right-click on the object in SQL Server Management Studio to rename it. However, if you want to make the renaming process a little more controlled and preferably distributable, then you can’t avoid scripting the process and don’t need the procedure ‘SP_RENAME’.

How can you use SP_RENAME?

SP_RENAME renames user-created objects, which can be almost any object from database to tables and indexes to CLR types (provided you have ALTER permissions on the objects). Using SP_RENAME is quite easy, but be careful, if you run the following examples you can destroy your databases permanently, so as always in the Dojo, please use a non-critical game database, where it doesn’t matter if one or the other view doesn’t work anymore. We use the WideWorldImporters database in the Docker container. The simplest call is to rename the WideWorldImporters database first:

EXEC sp_rename 'WideWorldImporters', 'WWI', 'Database'

You can see that SP_RENAME gets three arguments, the name of the original object (in our case “WideWorldImporters”), the new name for the object (in our case “WWI”) and what kind of object it is. The third parameter is omitted if you rename tables, otherwise you must always specify what kind of object you are renaming here.

Now let’s switch to our renamed database and rename a table:

USE WWI;
EXEC sp_rename 'Purchasing.PurchaseOrderLines', 'POL'

What you can see in the result is that the new POL table is also in the purchasing scheme. But if you try to omit the schema for the first argument, the table to be renamed, you will get an error:

EXEC sp_rename 'POL', 'OrderLine'

No item by the name of 'POL' could be found in the current database 'WWI'.

The schema must therefore be specified here so that the table can be found. But what happens if we also specify the schema for the target object? Let’s try it out:

EXEC sp_rename 'Purchasing.POL', 'Purchasing.OrderLine'

The result is that in the “Purchasing” schema there is now a table with the name “Purchasing.OrderLine”. Since the table name with the period contains a special character, we would have to query this as follows

SELECT TOP(10)
    PurchaseOrderLineID 
FROM Purchasing.[Purchasing.OrderLine]

Why is that? Because changing the schema is not renaming the table, but an operation on the schema. If we want to move the table to a new schema, we use an ALTER SCHEMA ADD.

So let’s name the table reasonably for now:

EXEC sp_rename 'Purchasing.[Purchasing.OrderLine]', 'OrderLine'

Now let’s look at the table, it has a primary key and a clustered index with the name PK_Purchasing_PurchaseOrderLines which we want to adapt to the new table name, so we rename the index:

EXEC sp_rename 
     'Purchasing.OrderLine.PK_Purchasing_PurchaseOrderLines'
    ,'PK_Purchasing_OrderLines'
    ,'Index'

If you now look at the table, you will notice that SP_RENAME has renamed not only the index but also the associated primary key constraint. This means that SP_RENAME has made sure that your database is in order. And SP_RENAME has also kindly adapted the statistics for you.

How does it feel when we try it the other way around? Let’s rename a statistic that belongs to an index and a foreign key:

EXEC sp_rename
     'Purchasing.OrderLine.FK_Purchasing_PurchaseOrderLines_PackageTypeID'
    ,'FK_Purchasing_OrderLines_PackageTypeID'
    ,'Statistics'

In this case, the index associated with the statistics was also renamed, but the foreign key relationship was not.

And what happens if we rename a column with the indexes and foreign key relationships in which it occurs? Let’s give it a try:

EXEC sp_rename
     'Purchasing.OrderLine.PurchaseOrderLineID'
    ,'OrderLineID'
    ,'Column'

If we now look at the primary key or the clustered index, we see that the correct new column name has been adopted here as well. To check whether this also works for foreign key relationships, we rename an object referenced by the OrderLine:

EXEC sp_rename
     'Purchasing.PurchaseOrders.PurchaseOrderID'
    ,'OrderID'
    ,'Column'

If we now look at the OrderLines table, we see that the foreign key relationship that refers to the Order has been correctly adjusted:

ALTER TABLE [Purchasing].[OrderLine]  WITH CHECK 
ADD CONSTRAINT [FK_Purchasing_PurchaseOrderLines_PurchaseOrderID_Purchasing_PurchaseOrders]
FOREIGN KEY([PurchaseOrderID])
REFERENCES [Purchasing].[PurchaseOrders] ([OrderID])

What you hopefully learned today is that while it would be desirable to avoid refactoring your database from the beginning, that would not be realistic at all. So when it comes to refactoring, the SP_RENAME procedure is a welcome tool, and it does quite a bit of the work in many places to ensure that your renaming does not at least leave you in a state where your database is fundamentally flawed (foreign keys that run out of space, threatening the referential integrity of the database). However, even SP_RENAME cannot do everything: you still have to take care of the correctness of your applications, views, scripts and procedures after renaming. So be careful when renaming!

References

• Documentation from Microsoft