When you rename a table on your development database, this change will be interpreted as a DROP and CREATE.

To avoid this data loss, you can write a migration script to rename the table using the sp_rename stored procedure. This script replaces the DROP and CREATE statements the SQL Compare engine would otherwise generate for this change.

In this example, we'll use a migration script to rename a table in our development database.

1. Set up the databases

1. In SSMS, run this script on your server and refresh Object Explorer.

   SQL creation script

   USE master
   GO
   IF DB_ID('talkr_dev') IS NOT NULL
   BEGIN
        ALTER DATABASE talkr_dev SET SINGLE_USER WITH ROLLBACK IMMEDIATE
        DROP DATABASE talkr_dev
   END
   GO
   IF DB_ID('talkr_prod') IS NOT NULL
   BEGIN
        ALTER DATABASE talkr_prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE
        DROP DATABASE talkr_prod
   END
   GO
    
   CREATE DATABASE talkr_dev
   GO
   USE talkr_dev
   GO
   CREATE TABLE [dbo].[users] (
           title varchar(10),
           name NCHAR(50),
           city varchar(25))
   GO
   INSERT INTO users ([title],[name],[city]) VALUES ('Mr', 'Miles Davis', 'Santa Monica')
   INSERT INTO users ([title],[name],[city]) VALUES ('Mrs', 'Ella Fitzgerald', 'Beverly Hills')
   INSERT INTO users ([title],[name],[city]) VALUES ('Mr', 'Glenn Miller', 'Bedford')
   INSERT INTO users ([title],[name],[city]) VALUES ('Master', 'Benjamin Clementine', 'London')
   GO
    
   CREATE DATABASE talkr_prod
   GO
   USE talkr_prod
   GO
   CREATE TABLE [dbo].[users] (
           title varchar(10),
           name NCHAR(50),
           city varchar(25))
   GO
   INSERT INTO users ([title],[name],[city]) VALUES ('Mr', 'Miles Davis', 'Santa Monica')
   INSERT INTO users ([title],[name],[city]) VALUES ('Mrs', 'Ella Fitzgerald', 'Beverly Hills')
   INSERT INTO users ([title],[name],[city]) VALUES ('Mr', 'Glenn Miller', 'Bedford')
   INSERT INTO users ([title],[name],[city]) VALUES ('Master', 'Benjamin Clementine', 'London')
   GO

   Two identical databases are created:

   • • talkr_dev (our development environment)
     • talkr_prod (our production environment)

   Both databases contain a table named people, populated with data.

   If these databases already exist on your server, executing this script will drop and create them again.

2. The migration script to rename the table

Now that our environments are set up, we'll rename the users table on our development environment (talkr_dev)

Replace the autogenerated script with the following SQL.

EXEC sp_rename '[dbo].[users]', 'customers'
GO
CREATE SYNONYM dbo.users FOR customers
GO