Search code examples
sqlsql-servercreate-tablealter-table

Create table or only add changed/new columns

I have several tables which are worked on within a development environment, then moved to production. If they don't already exist in production, it's fine to just generate the table creation script from SSMS and run it. However, there are occasions where the table already exists in production but all that's needed is an extra column or constraint. The problem is knowing exactly what has changed.

Is there a way to get SQL to compare my CREATE TABLE statement against the existing table and only apply what has changed? Essentially I am trying to do the below and SQL correctly complains that the table exists already.

I would have to manually write an ALTER query which on a real example would be difficult due to the sheer volume of columns. Is there a better / easier way to see what has changed? Note that this involves two separate database servers.

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50),
  contact_number char(20), --this has been added
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

Also, dropping and recreating wouldn't be a possibility because data would be lost.

Solution

  • SSMS can generate the schema change script if you make the change in the table designer (right-click on the table in Object Explorer and select Design). Then, instead of applying the change immediately, from the menu select Table Designer-->Generate Change Script. Note that depending on the change, SSMS may need to recreate the table, although data will be retained. SSMS requires you uncheck the option to "prevent saving changes that require table re-creation" under Tools-->Options-->Designers-->Table and Database Designers. Review the script to make sure you're good with it.

    SQL Server Data Tools (SSDT) and third-party tools (e.g. from Red-Gate and ApexSQL) have schema-compare features to generate the needed DDL after the fact. There are also features like migration scripts to facilitate continuous integration and source control integration as well. I suggest you keep database objects under source control and leverage database tooling as part of your development process.