static void Work(Argument arguments) { try { DBDiff.Schema.SQLServer.Generates.Model.Database origen; DBDiff.Schema.SQLServer.Generates.Model.Database destino; if (TestConnection(arguments.ConnectionString1, arguments.ConnectionString2)) { Generate sql = new Generate(); SqlOption opt = new SqlOption(true); opt.Ignore.FilterSchema = false; opt.Ignore.FilterUsers = false; opt.Ignore.FilterTableFileGroup = false; sql.Options = opt; sql.ConnectionString = arguments.ConnectionString1; System.Console.WriteLine("Reading first database..."); //sql.Options = SqlFilter; origen = sql.Process(); sql.ConnectionString = arguments.ConnectionString2; System.Console.WriteLine("Reading second database..."); destino = sql.Process(); System.Console.WriteLine("Comparing databases schemas..."); origen = Generate.Compare(origen, destino); System.Console.WriteLine("Generating SQL file..."); SaveFile(arguments.OutputFile, origen.ToSql()); } } catch (Exception ex) { System.Console.WriteLine(ex.Message); } }
private static string GetSQL(SqlOption options) { string filter = ""; string sql = ""; sql += "SELECT O.name, O.type, M.object_id, OBJECT_DEFINITION(M.object_id) AS Text FROM sys.sql_modules M "; sql += "INNER JOIN sys.objects O ON O.object_id = M.object_id "; sql += "WHERE "; if (options.Ignore.FilterStoreProcedure) filter += "O.type = 'P' OR "; if (options.Ignore.FilterView) filter += "O.type = 'V' OR "; if (options.Ignore.FilterTrigger) filter += "O.type = 'TR' OR "; if (options.Ignore.FilterFunction) filter += "O.type IN ('IF','FN','TF') OR "; filter = filter.Substring(0, filter.Length - 4); return sql + filter; }
public PrincipalForm() { InitializeComponent(); SqlFilter = new SqlOption(); }
private static string GetSQL(DatabaseInfo.VersionTypeEnum version, SqlOption options) { string sql = ""; sql += "SELECT T.object_id, O.type AS ObjectType, ISNULL(CONVERT(varchar,AM.execute_as_principal_id),'CALLER') as ExecuteAs, AF.name AS assembly_name, AM.assembly_class, AM.assembly_id, AM.assembly_method, T.type, CAST(ISNULL(tei.object_id,0) AS bit) AS IsInsert, CAST(ISNULL(teu.object_id,0) AS bit) AS IsUpdate, CAST(ISNULL(ted.object_id,0) AS bit) AS IsDelete, T.parent_id, S.name AS Owner,T.name,is_disabled,is_not_for_replication,is_instead_of_trigger "; sql += "FROM sys.triggers T "; sql += "INNER JOIN sys.objects O ON O.object_id = T.parent_id "; sql += "INNER JOIN sys.schemas S ON S.schema_id = O.schema_id "; sql += "LEFT JOIN sys.trigger_events AS tei ON tei.object_id = T.object_id and tei.type=1 "; sql += "LEFT JOIN sys.trigger_events AS teu ON teu.object_id = T.object_id and teu.type=2 "; sql += "LEFT JOIN sys.trigger_events AS ted ON ted.object_id = T.object_id and ted.type=3 "; if (version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10) { sql += ",(SELECT null as execute_as_principal_id, null as assembly_class, null as assembly_id, null as assembly_method) AS AM,"; sql += "(SELECT null AS name) AS AF"; } else { sql += "LEFT JOIN sys.assembly_modules AM ON AM.object_id = T.object_id "; sql += "LEFT JOIN sys.assemblies AF ON AF.assembly_id = AM.assembly_id"; } sql += " ORDER BY T.parent_id"; return sql; }
/// <summary> /// Constructor de la clase. /// </summary> /// <param name="connectioString">Connection string de la base</param> public GenerateDatabase(string connectioString, SqlOption filter) { this.connectioString = connectioString; this.objectFilter = filter; }
public new void Load(SqlOption valueOption) { this.option = valueOption; txtBlob.Text = option.Defaults.DefaultBlobValue; txtDate.Text = option.Defaults.DefaultDateValue; txtDefaultInteger.Text = option.Defaults.DefaultIntegerValue; txtDefaultReal.Text = option.Defaults.DefaultRealValue; txtNText.Text = option.Defaults.DefaultNTextValue; txtText.Text = option.Defaults.DefaultTextValue; txtVariant.Text = option.Defaults.DefaultVariantValue; txtTime.Text = option.Defaults.DefaultTime; txtXML.Text = option.Defaults.DefaultXml; chkCompAssemblys.Checked = option.Ignore.FilterAssemblies; chkCompCLRFunctions.Checked = option.Ignore.FilterCLRFunction; chkCompCLRStore.Checked = option.Ignore.FilterCLRStoreProcedure; chkCompCLRTrigger.Checked = option.Ignore.FilterCLRTrigger; chkCompCLRUDT.Checked = option.Ignore.FilterCLRUDT; chkConstraints.Checked = option.Ignore.FilterConstraint; chkConstraintsPK.Checked = option.Ignore.FilterConstraintPK; chkConstraintsFK.Checked = option.Ignore.FilterConstraintFK; chkConstraintsUK.Checked = option.Ignore.FilterConstraintUK; chkConstraintsCheck.Checked = option.Ignore.FilterConstraintCheck; chkCompExtendedProperties.Checked = option.Ignore.FilterExtendedPropertys; chkCompFunciones.Checked = option.Ignore.FilterFunction; chkIndex.Checked = option.Ignore.FilterIndex; chkIndexFillFactor.Checked = option.Ignore.FilterIndexFillFactor; chkIndexIncludeColumns.Checked = option.Ignore.FilterIndexIncludeColumns; chkIndexFilter.Checked = option.Ignore.FilterIndexFilter; chkFullText.Checked = option.Ignore.FilterFullText; chkFullTextPath.Checked = option.Ignore.FilterFullTextPath; chkCompSchemas.Checked = option.Ignore.FilterSchema; chkCompStoreProcedure.Checked = option.Ignore.FilterStoreProcedure; chkTableOption.Checked = option.Ignore.FilterTableOption; chkTables.Checked = option.Ignore.FilterTable; chkTablesColumnIdentity.Checked = option.Ignore.FilterColumnIdentity; chkTablesColumnCollation.Checked = option.Ignore.FilterColumnCollation; chkTableLockEscalation.Checked = option.Ignore.FilterTableLockEscalation; chkTableChangeTracking.Checked = option.Ignore.FilterTableChangeTracking; chkTablesColumnOrder.Checked = option.Ignore.FilterColumnOrder; chkIgnoreNotForReplication.Checked = option.Ignore.FilterNotForReplication; chkCompTriggersDDL.Checked = option.Ignore.FilterDDLTriggers; chkCompTriggers.Checked = option.Ignore.FilterTrigger; chkCompUDT.Checked = option.Ignore.FilterUserDataType; chkCompVistas.Checked = option.Ignore.FilterView; chkCompXMLSchemas.Checked = option.Ignore.FilterXMLSchema; chkFileGroups.Checked = option.Ignore.FilterTableFileGroup; chkCompUsers.Checked = option.Ignore.FilterUsers; chkCompRoles.Checked = option.Ignore.FilterRoles; chkCompRules.Checked = option.Ignore.FilterRules; if (option.Script.AlterObjectOnSchemaBinding) optScriptSchemaBindingAlter.Checked = true; else optScriptSchemaDrop.Checked = true; if (option.Comparison.CaseSensityType == SqlOptionComparison.CaseSensityOptions.Automatic) rdoCaseAutomatic.Checked = true; if (option.Comparison.CaseSensityType == SqlOptionComparison.CaseSensityOptions.CaseInsensity) rdoCaseInsensitive.Checked = true; if (option.Comparison.CaseSensityType == SqlOptionComparison.CaseSensityOptions.CaseSensity) rdoCaseSensitive.Checked = true; if (option.Comparison.CaseSensityInCode == SqlOptionComparison.CaseSensityOptions.CaseInsensity) rdoCaseInsensityInCode.Checked = true; if (option.Comparison.CaseSensityInCode == SqlOptionComparison.CaseSensityOptions.CaseSensity) rdoCaseSensityInCode.Checked = true; chkIgnoreWhiteSpaceInCode.Checked = option.Comparison.IgnoreWhiteSpacesInCode; LoadFilters(); }
private SqlOption BuildOptions() { var config = (Configuration) Configuration; var options = new SqlOption(false) { Ignore = { FilterConstraint = config[OptionType.FilterConstraint].ConfiguredValue, FilterFunction = config[OptionType.FilterFunction].ConfiguredValue, FilterStoreProcedure = config[OptionType.FilterStoreProcedure].ConfiguredValue, FilterView = config[OptionType.FilterView].ConfiguredValue, FilterTable = config[OptionType.FilterTable].ConfiguredValue, FilterTableOption = config[OptionType.FilterTableOption].ConfiguredValue, FilterUserDataType = config[OptionType.FilterUserDataType].ConfiguredValue, FilterTrigger = config[OptionType.FilterTrigger].ConfiguredValue, FilterSchema = config[OptionType.FilterSchema].ConfiguredValue, FilterXMLSchema = config[OptionType.FilterXMLSchema].ConfiguredValue, FilterTableFileGroup = config[OptionType.FilterTableFileGroup].ConfiguredValue, FilterExtendedPropertys = config[OptionType.FilterExtendedProperties].ConfiguredValue, FilterDDLTriggers = config[OptionType.FilterDDLTriggers].ConfiguredValue, FilterSynonyms = config[OptionType.FilterSynonyms].ConfiguredValue, FilterRules = config[OptionType.FilterRules].ConfiguredValue, FilterAssemblies = config[OptionType.FilterAssemblies].ConfiguredValue, FilterTableChangeTracking = config[OptionType.FilterTableChangeTracking].ConfiguredValue, FilterTableLockEscalation = config[OptionType.FilterTableLockEscalation].ConfiguredValue, FilterFullTextPath = config[OptionType.FilterFullTextPath].ConfiguredValue, FilterFullText = config[OptionType.FilterFullText].ConfiguredValue, FilterCLRStoreProcedure = config[OptionType.FilterCLRStoredProcedure].ConfiguredValue, FilterCLRUDT = config[OptionType.FilterCLRUserDefinedType].ConfiguredValue, FilterCLRTrigger = config[OptionType.FilterCLRTrigger].ConfiguredValue, FilterCLRFunction = config[OptionType.FilterCLRFunction].ConfiguredValue, FilterRoles = config[OptionType.FilterRoles].ConfiguredValue, FilterUsers = config[OptionType.FilterUsers].ConfiguredValue, FilterNotForReplication = config[OptionType.FilterNotForReplication].ConfiguredValue, FilterColumnCollation = config[OptionType.FilterColumnCollation].ConfiguredValue, FilterColumnIdentity = config[OptionType.FilterColumnIdentity].ConfiguredValue, FilterColumnOrder = config[OptionType.FilterColumnOrder].ConfiguredValue, FilterIndexRowLock = config[OptionType.FilterIndexRowLock].ConfiguredValue, FilterIndexIncludeColumns = config[OptionType.FilterIndexIncludeColumns].ConfiguredValue, FilterIndexFillFactor = config[OptionType.FilterIndexFillFactor].ConfiguredValue, FilterConstraintCheck = config[OptionType.FilterConstraintCheck].ConfiguredValue, FilterConstraintUK = config[OptionType.FilterConstraintUniqueKey].ConfiguredValue, FilterConstraintFK = config[OptionType.FilterConstraintForeignKey].ConfiguredValue, FilterConstraintPK = config[OptionType.FilterConstraintPrimaryKey].ConfiguredValue, FilterIndex = config[OptionType.FilterIndex].ConfiguredValue, FilterIndexFilter = config[OptionType.FilterIndexFilter].ConfiguredValue, FilterPartitionScheme = config[OptionType.FilterPartitionScheme].ConfiguredValue, FilterPartitionFunction = config[OptionType.FilterPartitionFunction].ConfiguredValue, }, Comparison = { IgnoreWhiteSpacesInCode = config[OptionType.ComparisonIgnoreWhiteSpacesInCode].ConfiguredValue, CaseSensityType = config[OptionType.FilterPartitionFunction].ConfiguredValue ? SqlOptionComparison.CaseSensityOptions.CaseSensity : SqlOptionComparison.CaseSensityOptions.Automatic, CaseSensityInCode = config[OptionType.FilterPartitionFunction].ConfiguredValue ? SqlOptionComparison.CaseSensityOptions.CaseSensity : SqlOptionComparison.CaseSensityOptions.Automatic, }, Script = { AlterObjectOnSchemaBinding = config[OptionType.ScriptAlterObjectOnSchemaBinding].ConfiguredValue } }; return options; }
public void Show(IWin32Window owner, SqlOption filter) { SqlFilter = filter; sqlOptionsFront1.Load(filter); this.Show(owner); }