The best way to rename the tables after development completed

Question

What is the easiest and most reliable approach to rename the database tables and columns in SQL Server 2008 r2? We have finished development and for some reasons, we are required to rename some tables and afew columns.

Is using synonyms the good way of doing that? What are the pitfalls we should be prepared for? Does it help in renaming the columns gracefully as well?

We have a lot of scripts related to these tables and also they are referenced in the application by .net developers.

Is the intention to rename these database objects and provide a facade so that existing apps do not need to be modified to use the new object names? — billinkc, Oct 11 '12 at 21:35

Aaron Bertrand · Accepted Answer · 2012-10-11T23:27:30.737

I think the approach depends on whether the application(s) are live or if you are still testing.

For tables, the safest approach is to create a synonym using the new name. This way you can change the app(s) one at a time (or even one reference at a time), without having to change all of them at once. You don't have to drop the synonym and rename the table until you are confident you have all the changes in place.

CREATE SYNONYM dbo.NewName FOR dbo.OldName;
-- change app to point to dbo.NewName;

-- once all of your changes have been tested:
BEGIN TRANSACTION;
  DROP SYNONYM dbo.NewName;
  EXEC sp_rename N'dbo.OldName', N'NewName', N'OBJECT';
COMMIT TRANSACTION;

For columns, it is a little trickier. You can create synonyms that point to a view instead, but not all views will necessarily be updatable depending on the base table. As a simple example:

CREATE VIEW dbo.vNewName 
AS 
  SELECT Column1, NewColumnName = OldColumnName
    FROM dbo.OldName;

CREATE SYNONYM dbo.NewName FOR dbo.vNewName;

Then like above, when you have changed all references to columns and the new table name, simply:

BEGIN TRANSACTION;
  DROP SYNONYM dbo.NewName;
  DROP VIEW dbo.vNewName;
  EXEC sp_rename N'dbo.OldName', N'NewName', N'OBJECT';
  EXEC sp_rename N'dbo.NewName.OldColumnName', N'NewColumnName', N'COLUMN';
COMMIT TRANSACTION;

If the application is not live and is still going through testing, just rename the columns and fix what breaks after a global search and replace (or smart refactor using SSDT, RedGate, etc) through app code / procedures etc.

If the application is live, you will need to step a little more gingerly.

+1 for validating the change it and fix what broke approach that I get crap for from my own team (dev enviro only of course) — RThomas, Oct 11 '12 at 23:47
Thanks for your good answer, but would you be able to advise me what you mean by : "This way you can change the app(s) one at a time (or even one reference at a time), without having to change all of them at once. ". Do you mean we should create the synonym one at the time for example first table t1, do the code changes, then table t2, etc? — Sky, Oct 12 '12 at 01:35
We are now at the development phase, but system has almost been completed for that component which these tables are to be renamed. System is now deployed to UAT. — Sky, Oct 12 '12 at 01:38
@Nazila no, I mean that you can create a synonym for table1 (and one for table2, table3, etc.). Then you can change one app, or one class, or one module, or one stored procedure to use the new name via the synonym. The rest of the code can continue to use the old name. You wouldn't drop the synonym and rename until all of the code was updated. — Aaron Bertrand, Oct 12 '12 at 02:34

score 0 · Answer 2 · answered Oct 11 '12 at 21:20

Well there are many pitfalls you can run into when changing the name of tables and columns. Any other SQL or application code that calls them is going to have to be renamed to match or it will not work. Redgate makes some pretty good tools that can go through and make all the changes to dependent SQL veiws/functions/sprocs. The biggest piece of advise that I can give you is to do these changes in a DEV environment first and TEST TEST TEST to make sure you have fixed the code everywhere.

The best way to rename the tables after development completed

2 Answers2