/// <summary> /// Drop an object and generate the script to re-add it /// </summary> /// <param name="urn">The object to drop</param> /// <param name="addObjects">The list of addObjects, or null to not re-add dependencies</param> /// <param name="options">Options for scripting</param> private void DropAndReAdd (Urn urn, List<SchemaObject> addObjects, TableScriptOptions options) { // generate the script to readd if (addObjects != null) { _scripter.Options.ScriptDrops = false; _scripter.Options.IncludeIfNotExists = true; string addScript = GenerateScript (urn); // unnamed primary keys and constraints need to not be auto-added, since they must be built into the table if ((options & TableScriptOptions.ScriptAnonymousConstraints) != 0) addScript = _anonymousReferenceRegex.Replace (addScript, "IF 0=1 $0"); else { addScript = _anonymousRegex.Replace (addScript, "IF 0=1 $0"); addScript = _anonymousDefaultRegex.Replace (addScript, "IF 0=1 $0"); } // if the database has autostatistics, then skip all statistics addScript = _statisticsRegex.Replace (addScript, ""); // create triggers must be the first statement in the batch int pos = addScript.IndexOf("CREATE TRIGGER", StringComparison.OrdinalIgnoreCase); if (pos >= 0) addScript = addScript.Substring (pos); // remove primary xml indexes if we don't need them if ((options & TableScriptOptions.PrimaryXmlIndexes) == 0) addScript = _primaryXmlIndex.Replace (addScript, "IF 0=1 $0"); if (addScript.Length > 0) { SchemaObject newObject = new SchemaObject (SchemaObjectType.Script, "Scripted Dependencies", addScript); if ((options & TableScriptOptions.AddAtEnd) != 0) addObjects.Add (newObject); else addObjects.Insert (0, newObject); } } // script the drop of everything _scripter.Options.ScriptDrops = true; _scripter.Options.IncludeIfNotExists = true; string dropScript = GenerateScript (urn); // the scripter should not be scripting the table drop, so we have to comment it out // note that the !PrimaryObject option above works for the create script SqlSmoObject smo = _scripter.Server.GetSmoObject (urn); Table table = smo as Table; if (table != null) dropScript = _dropTableRegex.Replace (dropScript, "SELECT 1"); // smo switches to master, so switch back to our db _connection.ChangeDatabase (_databaseName); if (!String.IsNullOrWhiteSpace(dropScript)) ExecuteNonQuery (dropScript); ResetScripter (); }
/// <summary> /// Drops all of the dependencies on a table so the table can be updated. /// </summary> /// <param name="tableName">The table to update</param> /// <param name="addObjects">The list of addObjects, or null to not re-add dependencies</param> /// <param name="tableUpdates">The list of updated being made to tables. Need to prevent duplicate changes</param> /// <param name="options">Options for scripting</param> /// <remarks>Drops the dependencies and adds SchemaObjects to readd the dependencies later</remarks> private void DropTableDepencencies(string tableName, List<SchemaObject> addObjects, TableScriptOptions options, bool modifyingTable) { try { Table table = _database.Tables[SchemaObject.UnformatSqlName (tableName)]; if (table == null) return; DependencyTree tree = _scripter.DiscoverDependencies (new SqlSmoObject[] { table }, DependencyType.Children); // find all of the tables that refer to this table and drop all of the foreign keys for (DependencyTreeNode dependent = tree.FirstChild.FirstChild; dependent != null; dependent = dependent.NextSibling) { if (dependent.Urn.Type == "Table") { // script the re-add of foreign keys, but not the tables themselves _scripter.Options = new ScriptingOptions (ScriptOption.DriForeignKeys) - ScriptOption.PrimaryObject; // don't script anonymous constraints. // if they are on the table, they will get created with the new table // if they are not on the table, they should go away options &= ~TableScriptOptions.ScriptAnonymousConstraints; DropAndReAdd (dependent.Urn, addObjects, options); } else if (modifyingTable && dependent.Urn.Type == "View") { DropViewDependencies (dependent.Urn, addObjects); DropAndReAdd (dependent.Urn, addObjects, options); } } // handle xml indexes separately if ((options & TableScriptOptions.AllXmlIndexes) != 0) { // drop all of the permissions, constraints, etc. on this table, but not the table itself _scripter.Options = new ScriptingOptions (); _scripter.Options.XmlIndexes = true; _scripter.Options -= ScriptOption.PrimaryObject; TableScriptOptions xmlOptions = options; if ((options & TableScriptOptions.PrimaryXmlIndexes) != 0) xmlOptions &= ~TableScriptOptions.AddAtEnd; DropAndReAdd (tree.FirstChild.Urn, addObjects, xmlOptions); } // drop the objects on the table itself if ((options & TableScriptOptions.IncludeTableModifiers) != 0) { options &= ~TableScriptOptions.ScriptAnonymousConstraints; // drop all of the permissions, constraints, etc. on this table, but not the table itself _scripter.Options = new ScriptingOptions (); _scripter.Options.DriAll = true; _scripter.Options.NonClusteredIndexes = true; _scripter.Options.ClusteredIndexes = true; _scripter.Options -= ScriptOption.PrimaryObject; DropAndReAdd (tree.FirstChild.Urn, addObjects, options); // script the permissions on the table ScriptPermissions (tree.FirstChild.Urn, addObjects); _scripter.Options = new ScriptingOptions (ScriptOption.Triggers); DropAndReAdd (tree.FirstChild.Urn, addObjects, options); } } finally { _connection.ChangeDatabase (_databaseName); } }