System.Data.SqlTypes true #define TRACE void Main() { string tableName = ""; using (BounceForeignKeys (tableName)) ExecuteCommand ("alter table ........."); } public IDisposable BounceForeignKeys (string tableName) { var keys = DropForeignKeyBuilder (tableName); return Disposable.Create (() => AddForeignKeyBuilder (keys)); } public IList DropForeignKeyBuilder( string tableName ) { var sql = new StringBuilder(); sql.AppendLine( @"DECLARE @error_line int" ); sql.AppendLine( @" , @error_number int" ); sql.AppendLine( @" , @error_message nvarchar(4000)" ); sql.AppendLine( @" , @error_procedure nvarchar(128)" ); sql.AppendLine( @" , @error_severity int" ); sql.AppendLine( @" , @error_state int" ); sql.AppendLine(); sql.AppendLine( @"BEGIN TRANSACTION" ); sql.AppendLine(); sql.AppendLine( @"BEGIN TRY" ); var keysReferenced = from fk in sys.Foreign_keys join rt in sys.Tables on fk.Referenced_object_id equals rt.Object_id join pt in sys.Tables on fk.Parent_object_id equals pt.Object_id where rt.Name == tableName select new KeyInfo { primary_object_id = pt.Object_id, referenced_object_id = rt.Object_id, PrimaryTableName = pt.Name, ReferencedTableName = rt.Name, fk_object_id = fk.Object_id, FkName = fk.Name, FkColumnNames = "", ReferencedTablePkColumnNames = "" }; // keysReferenced.Dump( "My foreign keys" ); var keysReferenced2 = ( from k in keysReferenced where false select k ).ToList(); foreach ( var k in keysReferenced ) { var foreignKeys = from fc in sys.Columns join fkc in sys.Foreign_key_columns on new { fc.Object_id, fc.Column_id } equals new { Object_id = fkc.Parent_object_id, Column_id = fkc.Parent_column_id } where k.primary_object_id == fkc.Parent_object_id && k.referenced_object_id == fkc.Referenced_object_id && k.fk_object_id == fkc.Constraint_object_id select new { fc.Name }; // foreignKeys.Dump( "My foreign key column names" ); string fkColumnNames = ""; string sep = ""; foreach ( var fk in foreignKeys ) { fkColumnNames += sep + fk.Name; sep = ", "; } var primaryKeys = from i in sys.Indexes join ic in sys.Index_columns on new { i.Object_id, i.Index_id } equals new { ic.Object_id, ic.Index_id } join pc in sys.Columns on new { ic.Object_id, ic.Column_id } equals new { pc.Object_id, pc.Column_id } where k.referenced_object_id == i.Object_id && i.Is_primary_key.Value select new { pc.Name }; // primaryKeys.Dump( "Primary key column names of referenced tables" ); string pkNames = ""; sep = ""; foreach ( var pk in primaryKeys ) { pkNames += sep + pk.Name; sep = ", "; } keysReferenced2.Add( new KeyInfo { primary_object_id = k.primary_object_id, referenced_object_id = k.referenced_object_id, PrimaryTableName = k.PrimaryTableName, ReferencedTableName = k.ReferencedTableName, fk_object_id = k.fk_object_id, FkName = k.FkName, FkColumnNames = fkColumnNames, ReferencedTablePkColumnNames = pkNames } ); } keysReferenced2.DumpT("Foreign Keys"); // Generate SQL foreach ( var k in keysReferenced2 ) { sql.AppendFormat( @" IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND parent_object_id = OBJECT_ID(N'[dbo].[{1}]')){2}" + @" ALTER TABLE [dbo].[{1}] DROP CONSTRAINT [{0}]{2}", k.FkName, k.PrimaryTableName, Environment.NewLine ); sql.AppendLine(); } sql.AppendLine( @" COMMIT" ); sql.AppendLine( @" SELECT 'Add foreign key constrained committed' AS [Message]" ); sql.AppendLine( @"END TRY" ); sql.AppendLine(); sql.AppendLine( @"BEGIN CATCH" ); sql.AppendLine( @" SELECT @error_line = ERROR_LINE()" ); sql.AppendLine( @" , @error_number = ERROR_NUMBER()" ); sql.AppendLine( @" , @error_message = ERROR_MESSAGE()" ); sql.AppendLine( @" , @error_procedure = ERROR_PROCEDURE()" ); sql.AppendLine( @" , @error_severity = ERROR_SEVERITY()" ); sql.AppendLine( @" , @error_state = ERROR_STATE()" ); sql.AppendLine(); sql.AppendLine( @" ROLLBACK" ); sql.AppendLine(); sql.AppendLine( @" PRINT 'Error occurred, transaction rolled back'" ); sql.AppendLine(); sql.AppendLine( @" SELECT @error_line AS ERROR_LINE" ); sql.AppendLine( @" , @error_number AS ERROR_NUMBER" ); sql.AppendLine( @" , @error_message AS ERROR_MESSAGE" ); sql.AppendLine( @" , @error_procedure AS ERROR_PROCEDURE" ); sql.AppendLine( @" , @error_severity AS ERROR_SEVERITY" ); sql.AppendLine( @" , @error_state AS ERROR_STATE" ); sql.AppendLine( @"END CATCH" ); sql.ToString().DumpT("DROP SQL"); ExecuteCommand( sql.ToString() ); return keysReferenced2; } public void AddForeignKeyBuilder( IList keysReferenced ) { var sql = new StringBuilder(); sql.AppendLine( @"DECLARE @error_line int" ); sql.AppendLine( @" , @error_number int" ); sql.AppendLine( @" , @error_message nvarchar(4000)" ); sql.AppendLine( @" , @error_procedure nvarchar(128)" ); sql.AppendLine( @" , @error_severity int" ); sql.AppendLine( @" , @error_state int" ); sql.AppendLine(); sql.AppendLine( @"BEGIN TRANSACTION" ); sql.AppendLine(); sql.AppendLine( @"BEGIN TRY" ); foreach ( var k in keysReferenced ) { sql.AppendFormat( " ALTER TABLE [dbo].[{0}] WITH CHECK ADD CONSTRAINT [{1}] FOREIGN KEY([{2}])\n" + " REFERENCES [dbo].[{3}] ([{4}]){5}" , new [] { k.PrimaryTableName, k.FkName, k.FkColumnNames, k.ReferencedTableName, k.ReferencedTablePkColumnNames, Environment.NewLine } ); sql.AppendLine(); sql.AppendFormat( @" ALTER TABLE [dbo].[{0}] CHECK CONSTRAINT [{1}]{2}", k.PrimaryTableName, k.FkName, Environment.NewLine ); sql.AppendLine(); } sql.AppendLine( @" COMMIT" ); sql.AppendLine( @" SELECT 'Add foreign key constrained committed' AS [Message]" ); sql.AppendLine( @"END TRY" ); sql.AppendLine(); sql.AppendLine( @"BEGIN CATCH" ); sql.AppendLine( @" SELECT @error_line = ERROR_LINE()" ); sql.AppendLine( @" , @error_number = ERROR_NUMBER()" ); sql.AppendLine( @" , @error_message = ERROR_MESSAGE()" ); sql.AppendLine( @" , @error_procedure = ERROR_PROCEDURE()" ); sql.AppendLine( @" , @error_severity = ERROR_SEVERITY()" ); sql.AppendLine( @" , @error_state = ERROR_STATE()" ); sql.AppendLine(); sql.AppendLine( @" ROLLBACK" ); sql.AppendLine(); sql.AppendLine( @" PRINT 'Error occurred, transaction rolled back'" ); sql.AppendLine(); sql.AppendLine( @" SELECT @error_line AS ERROR_LINE" ); sql.AppendLine( @" , @error_number AS ERROR_NUMBER" ); sql.AppendLine( @" , @error_message AS ERROR_MESSAGE" ); sql.AppendLine( @" , @error_procedure AS ERROR_PROCEDURE" ); sql.AppendLine( @" , @error_severity AS ERROR_SEVERITY" ); sql.AppendLine( @" , @error_state AS ERROR_STATE" ); sql.AppendLine( @"END CATCH" ); sql.ToString().DumpT("ADD SQL"); ExecuteCommand( sql.ToString() ); } public class KeyInfo { public int primary_object_id; public int referenced_object_id; public string PrimaryTableName; public string ReferencedTableName; public int fk_object_id; public string FkName; public string FkColumnNames; public string ReferencedTablePkColumnNames; } class Disposable : IDisposable { public static IDisposable Create (Action onDispose) { return new Disposable (onDispose); } Action _onDispose; Disposable (Action onDispose) { _onDispose = onDispose; } public void Dispose() { if (_onDispose != null) _onDispose (); _onDispose = null; } } static class Extensions { [Conditional("TRACE")] public static void DumpT (this object o, string heading = null) { o.Dump(heading); } }