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); }
}