How can a table definition be manually refreshed without also manually crafting a bunch of ALTER TABLE statements, or dropping and recreating the table?

This is normally handled by the publish process (e.g. SSDT), but during testing of schema alterations and their migration scripts, I need to revert the table definitions, and the managed rebuild/publish process through Visual Studio (and getting specific versions from TFS) takes quite a long time.

For example, if I have this new version of the table after updating the schema

CREATE TABLE [dbo].[MyAssociativeTable]
    [SurrogateID]   INT NOT NULL IDENTITY(1, 1)
    ,[ForeignID1]   INT NOT NULL FOREIGN KEY REFERENCES [MyOtherTable1]([ID])
    ,[Flag1]        BIT NOT NULL CONSTRAINT [DF_MyAssociativeTable_Flag1] DEFAULT(0)
    ,[ForeignID2]   INT NOT NULL FOREIGN KEY REFERENCES [MyOtherTable2]([ID])
    ,[Flag2]        BIT NOT NULL CONSTRAINT [DF_MyAssociativeTable_Flag2] DEFAULT(0)
    ,[DataColumn1]  REAL NULL CONSTRAINT [CK_MyAssociativeTable_Data1] CHECK(ISNULL([DataColumn1], 0) >= 0)
    ,[ExtraColumn1] VARCHAR(MAX) NULL
    ,CONSTRAINT [PK_MyAssociativeTable] PRIMARY KEY
    ,CONSTRAINT [CK_MyAssociativeTable_Case1] CHECK
        [Flag1] = 0
        OR [Flag2] = 0
CREATE UNIQUE INDEX [AK_MyAssociativeTable_F1]
ON [dbo].[MyAssociativeTable]([ForeignID1])
WHERE [Flag1] = 1

and I want to revert it to the original version (removing ExtraColumn1 is ok) so I can insert invalid rows to test migration scripting on

CREATE TABLE [dbo].[MyAssociativeTable]
    [SurrogateID]   INT IDENTITY(1, 1)
    ,[ForeignID1]   INT
    ,[ForeignID2]   INT
    ,[Flag1]        BIT
    ,[Flag2]        BIT
    ,[DataColumn1]  REAL
    ,PRIMARY KEY([SurrogateID])

what are my options?

Note that columns are also being re-ordered. Handling this would be nice, but is unimportant.

  • I would use a Schema Compare for this. Are you familiar with it? – Jacob H Sep 26 '19 at 16:24
  • No, we have never used that tool before, but should? – Elaskanator Sep 26 '19 at 17:45

One approach that works (only if there are no foreign keys referring to the table) is to create a backup of the original table with its original data.

Before starting to test the migration:

  1. Grab a copy of the old table definition to define a copy of it, with a unique name (e.g. append "_bak" to the table name) and all named constraints (using sp_rename on just the table name will not automatically update named constrains).
  3. Write a simple INSERT INTO [bak] (\every column\) SELECT \every column\ FROM [original] (the column list must be specified, otherwise you will get error 8101 for not explicitly writing out the identity column (why?))

Note 1: Make sure to not enable the advanced SSDT publish option "drop objects in target not in source" or this table will get dropped if you publish.

Note 2: Computed columns must not be included in the \every column\ list.

Then each time you want to revert the table definition and populate it with the preexisting records:

  1. DROP TABLE [target]
  2. Copy the original table definition and run that CREATE TABLE ... code
  4. INSERT INTO [target](\every column\) SELECT \every column\ FROM [bak]

This is still far too manual of a process however (and breaks on foreign keys).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.