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