/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/ BEGIN TRANSACTION SET XACT_ABORT ON SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CONTEXT_INFO 0x GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO ALTER TABLE dbo.Main DROP CONSTRAINT fk_Main_Lookup GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO ALTER TABLE dbo.Lookup SET (LOCK_ESCALATION = TABLE) GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO CREATE TABLE dbo.Tmp_Main ( KeyCol int NOT NULL, LookupId int NOT NULL, Constrained int NULL, DataCol int NULL ) ON [PRIMARY] GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO ALTER TABLE dbo.Tmp_Main SET (LOCK_ESCALATION = TABLE) GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO IF EXISTS(SELECT * FROM dbo.Main) EXEC('INSERT INTO dbo.Tmp_Main (KeyCol, LookupId, Constrained, DataCol) SELECT KeyCol, LookupId, Constrained, CONVERT(int, DataCol) FROM dbo.Main WITH (HOLDLOCK TABLOCKX)') GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO ALTER TABLE dbo.Sub DROP CONSTRAINT fk_Sub_Main GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO DROP TABLE dbo.Main GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO EXECUTE sp_rename N'dbo.Tmp_Main', N'Main', 'OBJECT' GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO ALTER TABLE dbo.Main ADD CONSTRAINT pk_Main PRIMARY KEY CLUSTERED ( KeyCol ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO CREATE NONCLUSTERED INDEX LookupId_ix ON dbo.Main ( LookupId ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO ALTER TABLE dbo.Main ADD CONSTRAINT ck_constrained CHECK (([Constrained]>(0))) GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO ALTER TABLE dbo.Main ADD CONSTRAINT fk_Main_Lookup FOREIGN KEY ( LookupId ) REFERENCES dbo.Lookup ( LookupId ) ON UPDATE NO ACTION ON DELETE NO ACTION GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO CREATE TRIGGER Audit_Main ON dbo.Main FOR INSERT, UPDATE AS INSERT Audit(TableName, KeyValue1, OtherCols) SELECT 'Main', ltrim(str(KeyCol)), (SELECT LookupId, Constrained, DataCol FOR XML RAW('Data')) FROM inserted GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO ALTER TABLE dbo.Sub ADD CONSTRAINT fk_Sub_Main FOREIGN KEY ( KeyCol ) REFERENCES dbo.Main ( KeyCol ) ON UPDATE NO ACTION ON DELETE NO ACTION GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO ALTER TABLE dbo.Sub SET (LOCK_ESCALATION = TABLE) GO IF @@error <> 0 AND @@trancount > 0 ROLLBACK TRANSACTION IF @@trancount = 0 BEGIN SET CONTEXT_INFO 0x01 BEGIN TRANSACTION END GO IF context_info() IS NULL COMMIT ELSE ROLLBACK TRANSACTION