Using sp_rename
-
Posted on June 18, 2010 by Derek Dieter
-
0
sp_rename is a system stored procedure that can rename objects. This procedure should be used with caution however, because when renaming an object that is referenced by other objects, will not update the name in the object reference. It is also a better practice to drop and recreate objects rather than using this procedure.
That being said, sp_rename can rename a number of object types. These object types are passed as parameters to the sp_rename function.
[cc lang=”sql”]
— Change the name of a table
EXEC sp_rename
‘dbo.Orders’ — Existing Table including Schema Name
,’OrderDetails’ — New Table Name
,’Object’ — Use Object designation for a table
— Change the name of a column
EXEC sp_rename
‘dbo.Orders.OrderAmount’, — Fully Qualified column name
‘OrderTotal’, — New column name
‘COLUMN’ — Object Type
— Change the name of an index
EXEC sp_rename
‘dbo.Orders.PK__Orders__C3905BAF6A30C649’, — Fully Qualified index name
‘IDX_PK_C_OrderID’, — New index name
‘INDEX’ — Object Type
[/cc]
- Comments (RSS)
- Trackback
- Permalink
Thanks!
don’t you need a comma after ‘dbo.Orders.OrderAmount’ ?
i.e.
— Change the name of a column
EXEC sp_rename
‘dbo.Orders.OrderAmount’ , — Fully Qualified column name
‘OrderTotal’, — New column name
‘COLUMN’
Yep, thanks for catching that. It’s now updated.
Derek
Derek,
Everything I’ve ever read cautions about using sp_rename and states a preference for dropping and re-creating. If I take that advice, how am I in a better position? I still have to go through the objects that reference via the old name and modify them.
Hi Mick,
From what I understand, the reason for dropping and recreating rather than performing sp_rename on an object is because sp_rename does not update the object reference in the sysobjects table. Rather, it simply changes a pointer. So basically it’s better to have an accurate sysobjects table. There’s probably a more technical explanation for it, but that’s essentially the idea.
Derek
