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