public FKStatement MigrateTableSql(IDbConnection connection, string currentName, string newName)
{
var fkStatement = new FKStatement();
fkStatement.DropStatement = "RENAME TABLE `" + currentName + "` TO `" + newName + "`;";
return(fkStatement);
}
/// <summary>
/// Generate migration SQL, base on individual Database, so we need to handle this by difference provider
/// </summary>
/// <param name="connection"></param>
/// <param name="currentName"></param>
/// <param name="newName"></param>
/// <returns></returns>
public FKStatement MigrateTableSql(IDbConnection connection, string currentName, string newName)
{
var fkStatement = new FKStatement();
//get the drop and re-create foreign keys sqls
var sql_get_foreign_keys = @"SELECT OBJECT_NAME(fk.parent_object_id) ParentObject,
OBJECT_NAME(fk.referenced_object_id) ReferencedObject,
'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
+ ' DROP CONSTRAINT ' + fk.NAME + ' ;' AS DropStatement,
'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
+ ' ADD CONSTRAINT ' + fk.NAME + ' FOREIGN KEY (' + COL_NAME(fk.parent_object_id, fkc.parent_column_id)
+ ') REFERENCES ' + ss.name + '.' + OBJECT_NAME(fk.referenced_object_id)
+ '(' + COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) + ');' AS CreateStatement
FROM
sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.schemas s ON fk.schema_id = s.schema_id
INNER JOIN sys.tables t ON fkc.referenced_object_id = t.object_id
INNER JOIN sys.schemas ss ON t.schema_id = ss.schema_id
WHERE
OBJECT_NAME(fk.referenced_object_id) = '" + currentName + "' AND ss.name = 'dbo';";
var fkSql = connection.SqlList <FKStatement>(sql_get_foreign_keys);
if (fkSql.Count > 0)
{
foreach (var fk in fkSql)
{
fkStatement.DropStatement += fk.DropStatement;
if (fk.ParentObject != currentName)
{
fkStatement.CreateStatement += fk.CreateStatement;
}
}
}
fkStatement.DropStatement += " select * into #temp from (select * from [" + currentName + "]) as tmp; drop table [" + currentName + "]; ";
return(fkStatement);
}
