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 ( [ForeignKey1] ,[ForeignKey2] ) ,CONSTRAINT [CK_MyAssociativeTable_Case1] CHECK ( [Flag1] = 0 OR [Flag2] = 0 ) ) GO 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.