/* 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 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 --COMMIT --BEGIN TRANSACTION GO ALTER TABLE dbo.Main DROP CONSTRAINT fk_Main_Lookup GO ALTER TABLE dbo.Lookup SET (LOCK_ESCALATION = TABLE) GO --COMMIT --BEGIN TRANSACTION GO CREATE TABLE dbo.Tmp_Main ( KeyCol int NOT NULL, LookupId int NOT NULL, Constrained int NULL, DataCol int NULL ) ON [PRIMARY] GO ALTER TABLE dbo.Tmp_Main SET (LOCK_ESCALATION = TABLE) 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 ALTER TABLE dbo.Sub DROP CONSTRAINT fk_Sub_Main GO DROP TABLE dbo.Main GO EXECUTE sp_rename N'dbo.Tmp_Main', N'Main', 'OBJECT' 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 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 ALTER TABLE dbo.Main ADD CONSTRAINT ck_constrained CHECK (([Constrained]>(0))) 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 CREATE TRIGGER Audit_Main ON dbo.Main FOR INSERT, UPDATE AS INSERT Audit(TableName, KeyValue1, OtherCols) SELECT 'Main', ltrim(str(KeyCol)), (SELECT LookupId, /* UselessCol, */ Constrained, DataCol FOR XML RAW('Data')) FROM inserted GO --COMMIT --BEGIN TRANSACTION 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 ALTER TABLE dbo.Sub SET (LOCK_ESCALATION = TABLE) GO COMMIT