/* 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