|
public GetRelationsWhereChild ( bool fullHierarchy = false ) : IEnumerable |
||
| fullHierarchy | bool | |
| return | IEnumerable |
|
public static string GetSqlRenameTable(Table oldTable, Table newTable)
{
//RENAME TABLE
var sb = new StringBuilder();
sb.AppendLine("--RENAME TABLE '" + oldTable.DatabaseName + "' TO '" + newTable.DatabaseName + "'");
sb.AppendLine("if exists(select * from sysobjects where name = '" + oldTable.DatabaseName + "' and xtype = 'U')");
sb.AppendLine("exec sp_rename '" + oldTable.DatabaseName + "', '" + newTable.DatabaseName + "';");
sb.AppendLine("GO");
sb.AppendLine();
if (newTable.EnforcePrimaryKey)
{
//RENAME PRIMARY KEY (it will be readded in create script)
var oldIndexName = "PK_" + oldTable.DatabaseName.ToUpper();
var newIndexName = "PK_" + newTable.DatabaseName.ToUpper();
sb.AppendLine("--RENAME PRIMARY KEY FOR TABLE '" + oldTable.DatabaseName + "'");
sb.AppendLine("if exists (select * from sys.indexes where name = '" + oldIndexName + "')");
sb.AppendLine("exec sp_rename '" + oldIndexName + "', '" + newIndexName + "';");
sb.AppendLine();
}
//Rename all FK
foreach (var relation in newTable.GetRelationsWhereChild())
{
var oldIndexName = "FK_" + relation.RoleName + "_" + oldTable.DatabaseName + "_" + relation.ParentTable.DatabaseName;
oldIndexName = oldIndexName.ToUpper();
var newIndexName = "FK_" + relation.RoleName + "_" + newTable.DatabaseName + "_" + relation.ParentTable.DatabaseName;
newIndexName = newIndexName.ToUpper();
sb.AppendLine("--RENAME FK [" + newTable.DatabaseName + "].[" + oldIndexName + "]");
sb.AppendLine("if exists (select * from sys.foreign_keys where name = '" + oldIndexName + "')");
sb.AppendLine("exec sp_rename @objname='" + newTable.GetSQLSchema() + "." + oldIndexName + "', @newname='" + newIndexName + "', @objtype='OBJECT';");
sb.AppendLine();
}
//Rename all indexes for this table's fields
foreach (var column in newTable.GetColumns())
{
var oldColumn = oldTable.GetColumns().FirstOrDefault(x => x.Key == column.Key);
if (oldColumn != null)
{
var oldIndexName = CreateIndexName(oldTable, oldColumn);
var newIndexName = CreateIndexName(newTable, column);
sb.AppendLine("--RENAME INDEX [" + newTable.DatabaseName + "].[" + oldIndexName + "]");
sb.AppendLine("if exists (select * from sys.indexes where name = '" + oldIndexName + "')");
sb.AppendLine("exec sp_rename @objname='" + newTable.GetSQLSchema() + "." + newTable.DatabaseName + "." + oldIndexName + "', @newname='" + newIndexName + "', @objtype='INDEX';");
sb.AppendLine();
}
}
//rename all indexes for this table
foreach (var index in newTable.TableIndexList)
{
var oldIndex = oldTable.TableIndexList.FirstOrDefault(x => x.Key == index.Key);
if (oldIndex != null)
{
var oldIndexName = GetIndexName(oldTable, oldIndex);
var newIndexName = GetIndexName(newTable, index);
sb.AppendLine("--RENAME INDEX [" + newTable.DatabaseName + "].[" + oldIndexName + "]");
sb.AppendLine("if exists (select * from sys.indexes where name = '" + oldIndexName + "')");
sb.AppendLine("exec sp_rename @objname='" + newTable.GetSQLSchema() + "." + newTable.DatabaseName + "." + oldIndexName + "', @newname='" + newIndexName + "', @objtype='INDEX';");
sb.AppendLine();
}
}
var model = newTable.Root as ModelRoot;
//Change the default name for all audit fields
if (oldTable.AllowCreateAudit)
{
var defaultName = ("DF__" + oldTable.DatabaseName + "_" + model.Database.CreatedDateColumnName).ToUpper();
var defaultName2 = ("DF__" + newTable.DatabaseName + "_" + model.Database.CreatedDateColumnName).ToUpper();
sb.AppendLine("--CHANGE THE DEFAULT NAME FOR CREATED AUDIT");
sb.AppendLine("if exists (select * from sys.default_constraints where name = '" + defaultName + "')");
sb.AppendLine("exec sp_rename @objname='" + defaultName + "', @newname='" + defaultName2 + "';");
sb.AppendLine();
}
if (oldTable.AllowModifiedAudit)
{
var defaultName = ("DF__" + oldTable.DatabaseName + "_" + model.Database.ModifiedDateColumnName).ToUpper();
var defaultName2 = ("DF__" + newTable.DatabaseName + "_" + model.Database.ModifiedDateColumnName).ToUpper();
sb.AppendLine("--CHANGE THE DEFAULT NAME FOR MODIFIED AUDIT");
sb.AppendLine("if exists (select * from sys.default_constraints where name = '" + defaultName + "')");
sb.AppendLine("exec sp_rename @objname='" + defaultName + "', @newname='" + defaultName2 + "';");
sb.AppendLine();
}
return sb.ToString();
}