The best way to rename the tables after development completed

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.

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.

StackExchange Database Administrators Q#25828, answer score: 10