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