SQL Change Automation 4

Renaming a table without data loss

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 Toggle source code

    1. USE master
    2. GO
    3. IF DB_ID('talkr_dev') IS NOT NULL
    4. BEGIN
    5. ALTER DATABASE talkr_dev SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    6. DROP DATABASE talkr_dev
    7. END
    8. GO
    9. IF DB_ID('talkr_prod') IS NOT NULL
    10. BEGIN
    11. ALTER DATABASE talkr_prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    12. DROP DATABASE talkr_prod
    13. END
    14. GO
    15. CREATE DATABASE talkr_dev
    16. GO
    17. USE talkr_dev
    18. GO
    19. CREATE TABLE [dbo].[users] (
    20. title varchar(10),
    21. name NCHAR(50),
    22. city varchar(25))
    23. GO
    24. INSERT INTO users ([title],[name],[city]) VALUES ('Mr', 'Miles Davis', 'Santa Monica')
    25. INSERT INTO users ([title],[name],[city]) VALUES ('Mrs', 'Ella Fitzgerald', 'Beverly Hills')
    26. INSERT INTO users ([title],[name],[city]) VALUES ('Mr', 'Glenn Miller', 'Bedford')
    27. INSERT INTO users ([title],[name],[city]) VALUES ('Master', 'Benjamin Clementine', 'London')
    28. GO
    29. CREATE DATABASE talkr_prod
    30. GO
    31. USE talkr_prod
    32. GO
    33. CREATE TABLE [dbo].[users] (
    34. title varchar(10),
    35. name NCHAR(50),
    36. city varchar(25))
    37. GO
    38. INSERT INTO users ([title],[name],[city]) VALUES ('Mr', 'Miles Davis', 'Santa Monica')
    39. INSERT INTO users ([title],[name],[city]) VALUES ('Mrs', 'Ella Fitzgerald', 'Beverly Hills')
    40. INSERT INTO users ([title],[name],[city]) VALUES ('Mr', 'Glenn Miller', 'Bedford')
    41. INSERT INTO users ([title],[name],[city]) VALUES ('Master', 'Benjamin Clementine', 'London')
    42. 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.


  1. EXEC sp_rename '[dbo].[users]', 'customers'
  2. GO
  3. CREATE SYNONYM dbo.users FOR customers
  4. GO

Creating a synonym for the customers table here will avoid issues where the table is referenced using the old name.


Didn't find what you were looking for?