Using sp_rename

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.

-- 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

5 comments
Eduardo 11 May 2014 at 3:00 pm
Tim Dim 31 Jan 2013 at 3:59 pm

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’

Derek Dieter 31 Jan 2013 at 5:57 pm

Yep, thanks for catching that. It’s now updated.

Derek

Mick O 24 Mar 2011 at 8:00 pm

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.

Derek Dieter 31 Mar 2011 at 5:37 pm

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

Featured Articles

 Site Author

  • Thanks for visiting!