/// <summary>
/// Create sql script for modifying given columns and all other columns containing foreign key references to these columns
/// </summary>
/// <param name="columns"></param>
public void GenerateScriptToAlterColumns(IEnumerable <ColumnIdentifier> columns)
{
var columnsByDatabase = columns.GroupBy(col => col.Database);
var generatedQueries = new GeneratedQueries();
foreach (var columnDbGroup in columnsByDatabase)
{
string database = columnDbGroup.Key;
generatedQueries.Database = database;
Console.WriteLine($"Creating scripts for database: {database}");
ConnectionStringBuilder.InitialCatalog = database;
using (var connection = new SqlConnection(ConnectionStringBuilder.ToString()))
{
connection.Open();
var modifyColumnSchema = new ModifyColumnSchemaScriptGenerator(connection, NewDataTypeName);
var queries = modifyColumnSchema.GenerateScript(columnDbGroup);
generatedQueries.AlterTableQueries.AddRange(queries);
}
}
generatedQueries.WriteOutputFiles();
}
/// <summary>
/// Create sql scripts for modifying columns that match the given pattern and all other columns containing foreign key references and
/// all sprocs, triggers, functions, user defined table types that contain references to the modified columns
/// </summary>
/// <param name="regex"></param>
/// <param name="database"></param>
/// <param name="schema"></param>
/// <param name="logDetails"></param>
/// <param name="ignoreObjectIdentifiers"></param>
public void GenerateScriptToAlterSchemaObjects(Regex regex, string database, string schema, bool logDetails = false,
IEnumerable <DatabaseSchemaObjectIdentifier> ignoreObjectIdentifiers = null)
{
ConnectionStringBuilder.InitialCatalog = database;
using (var connection = new SqlConnection(ConnectionStringBuilder.ToString()))
{
connection.Open();
var generatedQueries = new GeneratedQueries {
Database = database
};
Console.WriteLine($"Creating scripts for database: {database}");
var visitedColumnSet = new HashSet <ColumnIdentifier>();
ConnectionStringBuilder.InitialCatalog = database;
var modifyColumnSchema = new ModifyColumnSchemaScriptGenerator(connection, NewDataTypeName);
generatedQueries.AlterTableQueries.AddRange(modifyColumnSchema.GenerateScript(regex, schema, ref visitedColumnSet));
if (regex != null) // process sprocs, functions, and triggers
{
Console.WriteLine("Analyzing User Defined Table Types");
var modifyUserDefinedTableType = new ModifyUserDefinedTableTypeScriptGenerator(connection, NewDataTypeName, regex);
var userDefinedTableTypes = modifyUserDefinedTableType.GenerateScript();
generatedQueries.AlterUserDefinedTableTypeQueries.AddRange(userDefinedTableTypes);
var ignoreObjectIdList = ignoreObjectIdentifiers?.ToList();
var excludeSprocs = ignoreObjectIdList?.Where(id => id is SprocIdentifier).Cast <SprocIdentifier>();
var excludeFuncs = ignoreObjectIdList?.Where(id => id is FuncIdentifier).Cast <FuncIdentifier>();
var excludeTriggers = ignoreObjectIdList?.Where(id => id is TriggerIdentifier).Cast <TriggerIdentifier>();
Console.WriteLine("Analyzing Stored Procedures");
var modifySprocDefinition = new ModifySprocDefinitionScriptGenerator(connection, NewDataTypeName, regex);
generatedQueries.AlterSprocQueries.AddRange(modifySprocDefinition.GenerateScript(visitedColumnSet, excludeSprocs));
Console.WriteLine("Analyzing Functions");
var modifyFuncDefinition = new ModifyFuncDefinitionScriptGenerator(connection, NewDataTypeName, regex);
generatedQueries.AlterFuncQueries.AddRange(modifyFuncDefinition.GenerateScript(visitedColumnSet, excludeFuncs));
Console.WriteLine("Analyzing Triggers");
var modifyTriggerDefinition = new ModifyTriggerDefinitionScriptGenerator(connection, NewDataTypeName, regex);
generatedQueries.AlterTriggerQueries.AddRange(modifyTriggerDefinition.GenerateScript(visitedColumnSet, excludeTriggers));
if (logDetails)
{
CreateBeforeAndAfterFiles(database, "Sprocs", modifySprocDefinition.BeforeSqlText, modifySprocDefinition.AfterSqlText);
CreateBeforeAndAfterFiles(database, "Funcs", modifyFuncDefinition.BeforeSqlText, modifyFuncDefinition.AfterSqlText);
CreateBeforeAndAfterFiles(database, "Triggers", modifyTriggerDefinition.BeforeSqlText, modifyTriggerDefinition.AfterSqlText);
CreateBeforeAndAfterFiles(database, "TableType", modifyUserDefinedTableType.BeforeSqlText, modifyUserDefinedTableType.AfterSqlText);
}
}
generatedQueries.WriteOutputFiles();
}
}
