private void WhereUsedMenuItem_Click(object sender, EventArgs e)
{
TreeNode node;
TreeNode newNode = new TreeNode();
Urn[] urns = new Urn[1];
Scripter scripter;
// Get selected node
node = this.DependenciesTreeView.SelectedNode;
// Only do this once
if (node.Nodes.ContainsKey(Properties.Resources.UsedBy) == true
| node.Name == Properties.Resources.UsedBy)
{
return;
}
// Get the urn from the node
urns[0] = (Urn)(node.Tag);
// Add a "where used" node
newNode = new TreeNode(Properties.Resources.UsedBy);
newNode.Name = Properties.Resources.UsedBy;
node.Nodes.Add(newNode);
// And add the tree to the current node
scripter = new Scripter(server);
AddChildren(newNode, scripter.DiscoverDependencies(urns, DependencyType.Children).FirstChild);
node.Expand();
newNode.Expand();
}
private void DependenciesMenuItem_Click(object sender, EventArgs e)
{
Urn[] urns = new Urn[1];
Scripter scripter;
DependencyForm frm;
DependencyTree deps;
// Get the urn from the node
urns[0] = (Urn)(this.DependenciesTreeView.SelectedNode.Tag);
// Instantiate scripter
scripter = new Scripter(server);
// Get a new form
frm = new DependencyForm();
// Discover dependencies
deps = scripter.DiscoverDependencies(urns, DependencyType.Parents);
// Set the tree
frm.ShowDependencies(server, deps);
// Show the form
frm.Show();
}
private List <Urn> GetTableUrns(Database database, Scripter scripter)
{
//prepare root list of urns to walk through
var parentUrns = new List <Urn>();
foreach (Table table in database.Tables)
{
if (!table.IsSystemObject)
{
parentUrns.Add(table.Urn);
}
}
//collects dependency urns in the right order
var dependencyTree = scripter.DiscoverDependencies(parentUrns.ToArray(), true);
var dependencies = scripter.WalkDependencies(dependencyTree);
var dependencyUrns = new List <Urn>();
foreach (DependencyCollectionNode dependency in dependencies)
{
dependencyUrns.Add(dependency.Urn);
}
return(dependencyUrns);
}
/// <summary>
/// Event handler for where used menu item.
/// Adds a "USED BY" node and performs a dependency discovery after which
/// all dependents are added to the "USED BY" node.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void WhereUsedToolStripMenuItem_Click(object sender, EventArgs e)
{
TreeNode node, newNode;
node = DependenciesTreeView.SelectedNode;
if (node.Nodes.ContainsKey(Properties.Resources.UsedBy) ||
node.Name == Properties.Resources.UsedBy)
{
return;
}
newNode = new TreeNode(Properties.Resources.UsedBy);
newNode.Name = Properties.Resources.UsedBy;
node.Nodes.Add(newNode);
// Advanced Scripting
Scripter scripter = new Scripter(this.sqlServerSelection);
Urn[] urns = new Urn[1];
urns[0] = (Urn)node.Tag;
// Discover dependents
DependencyTree tree = scripter.DiscoverDependencies(urns, DependencyType.Children);
// Add to tree (recursive)
AddChildren(newNode, tree.FirstChild);
node.Expand();
newNode.Expand();
}
private static void GenereateData(Database db, Scripter scripter, StringBuilder sbScript)
{
var tbls = new Table[db.Tables.Count];
db.Tables.CopyTo(tbls, 0);
var tree = scripter.DiscoverDependencies(tbls, true);
var depwalker = new DependencyWalker();
var depcoll = depwalker.WalkDependencies(tree);
var sb = new StringBuilder();
//progressBar1.Maximum = sviews.Count;
foreach (DependencyCollectionNode dep in depcoll)
{
var o = db.Tables[dep.Urn.GetAttribute("Name")];
if (o != null && !o.IsSystemObject)
{
sb.AppendFormat("EXEC sp_generate_inserts @table_name='{0}', @owner='dbo'{1}", dep.Urn.GetAttribute("Name"), Environment.NewLine);
}
}
DataSet dset = db.ExecuteWithResults(sb.ToString());
foreach (DataTable dt in dset.Tables)
{
foreach (DataRow dr in dt.Rows)
{
sbScript.AppendLine(dr[0].ToString());
}
}
}
private void ShowDependenciesmenuItem_Click(object sender, EventArgs e)
{
Table table;
DependencyTree deps;
Scripter scripter;
SqlSmoObject[] smoObjects = new SqlSmoObject[1];
DependencyForm form = new DependencyForm();
// Just make sure something is selected
if (this.TableListView.SelectedIndices.Count == 0)
{
return;
}
// It's the first one as we only allow one selection
table = (Table)(this.TableListView.SelectedItems[0].Tag);
// Declare and instantiate new Scripter object
scripter = new Scripter(table.Parent.Parent);
// Declare array of SqlSmoObjects
smoObjects[0] = table;
// Discover dependencies
deps = scripter.DiscoverDependencies(smoObjects, true);
// Show dependencies
form.ShowDependencies(table.Parent.Parent, deps);
form.Show();
}
private void WhereUsedMenuItem_Click(object sender, EventArgs e)
{
TreeNode node;
TreeNode newNode = new TreeNode();
Urn[] urns = new Urn[1];
Scripter scripter;
// Get selected node
node = this.DependenciesTreeView.SelectedNode;
// Only do this once
if (node.Nodes.ContainsKey(Properties.Resources.UsedBy) == true
| node.Name == Properties.Resources.UsedBy)
{
return;
}
// Get the urn from the node
urns[0] = (Urn)(node.Tag);
// Add a "where used" node
newNode = new TreeNode(Properties.Resources.UsedBy);
newNode.Name = Properties.Resources.UsedBy;
node.Nodes.Add(newNode);
// And add the tree to the current node
scripter = new Scripter(server);
AddChildren(newNode, scripter.DiscoverDependencies(urns, DependencyType.Children).FirstChild);
node.Expand();
newNode.Expand();
}
public string ScriptObjects(ScriptingArgs args)
{
if (args == null || args.Objects == null || args.Objects.Count == 0)
{
return(String.Empty);
}
FireTaskProgressInfo("Preparing for scripting...");
scrp.Options = args.Options;
scrp.Options.ToFileOnly = false;
scrp.Options.AppendToFile = false;
scrp.Options.FileName = String.Empty;
scrp.Server.ConnectionContext.BatchSeparator = "GO";
StringCollection scr = null;
_recentErrors = null;
if (scrp.Options.ContinueScriptingOnError)
{
_recentErrors = new StringBuilder();
scrp.ScriptingError += new ScriptingErrorEventHandler(scrp_ScriptingError);
}
try
{
if (scrp.Options.WithDependencies)
{
scrp.DiscoveryProgress += new ProgressReportEventHandler(dWalk_DiscoveryProgress);
FireTaskProgressInfo("Discovering dependencies. (Task 1 of 3)");
DependencyTree dTree = scrp.DiscoverDependencies(PrepareObjectUrns(args.Objects), DependencyType.Parents);
FireTaskProgressInfo("Walking dependencies. (Task 2 of 3)");
DependencyCollection dCol = scrp.WalkDependencies(dTree);
FireTaskProgressInfo("Scripting objects. (Task 3 of 3)");
scr = scrp.ScriptWithList(dCol);
}
else
{
FireTaskProgressInfo("Scripting objects. (Task 1 of 1)");
scr = scrp.ScriptWithList(PrepareObjectUrns(args.Objects));
}
}
catch (Exception ex)
{
if (_cancelRequested)
{
return(String.Empty);
}
throw ex;
}
string myComment = "/***** Script generated with PragmaSQL Scripter on " + DateTime.Now.ToString() + " ******/";
string[] result = new string[scr.Count];
scr.CopyTo(result, 0);
return(myComment + "\r\n" + String.Join((String.IsNullOrEmpty(args.BatchSeparator) ? "\r\n" : "\r\n" + args.BatchSeparator + "\r\n"), result));
}
public static UrnCollection GetObjectsInDepedencyOrder(this Scripter sc, Urn[] objects)
{
var result = new UrnCollection();
var tree = sc.DiscoverDependencies(objects, true);
var walk = sc.WalkDependencies(tree);
foreach (var item in walk)
{
result.Add(item.Urn);
}
return(result);
}
private void dependenciesToolStripMenuItem_Click(object sender, EventArgs e)
{
if (this.ListView.SelectedItems.Count == 0)
{
return;
}
object selectedObject = this.ListView.SelectedItems[0].Tag;
IScriptable scriptableObject = selectedObject as IScriptable;
if (scriptableObject != null &&
!(selectedObject is Database))
{
SqlSmoObject[] smoObjects = new SqlSmoObject[1];
smoObjects[0] = (SqlSmoObject)selectedObject;
Scripter scripter = new Scripter(this.sqlServerSelection);
DependenciesForm frm = new DependenciesForm(this.sqlServerSelection,
scripter.DiscoverDependencies(smoObjects, DependencyType.Parents));
frm.ShowDialog(this);
}
}
static int Main(string[] args)
{
Console.OutputEncoding = System.Text.Encoding.UTF8;
var commandLineApplication = new CommandLineApplication();
commandLineApplication.Name = "sqlscripter";
commandLineApplication.Description = "Sqlscripter";
var sqlserver = commandLineApplication.Option("-S | --server", "Sql Server", CommandOptionType.SingleValue);
var sqluser = commandLineApplication.Option("-U | --user", "Sql User. Do not use in order to switch to integrated authentication.", CommandOptionType.SingleValue);
var sqlpsw = commandLineApplication.Option("-P | --psw", "Sql Password", CommandOptionType.SingleValue);
var sqldb = commandLineApplication.Option("-d | --database", "Sql Database", CommandOptionType.SingleValue);
var nouseprogress = commandLineApplication.Option("--no-progress", "Disable progress bar", CommandOptionType.NoValue);
commandLineApplication.Command("info", command =>
{
command.Options.AddRange(command.Parent.Options);
command.Description = $"{command.Name} render server information";
command.OnExecute(() =>
{
ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw);
if (null == serverConnection)
{
return(2);
}
Server server = new Server(serverConnection);
System.Console.WriteLine("Databases:");
foreach (var db in server.Databases)
{
System.Console.WriteLine($"\t{db}");
}
return(0);
});
});
commandLineApplication.Command("dbindex", command =>
{
command.Options.AddRange(command.Parent.Options);
command.Description = $"{command.Name} allow to connect to a database and build an ordered index of all objects";
var indexfile = command.Option("-i | --index", "Generate Index File", CommandOptionType.SingleValue);
var querymode = command.Option("--query-mode", "Use object query for objects", CommandOptionType.NoValue);
var one_stored = command.Option("--one-stored", "Generate one stored dependency", CommandOptionType.SingleValue);
var include_schemas = command.Option("-sc | --schema", "Database schemas to include in the output", CommandOptionType.MultipleValue);
command.OnExecute(() =>
{
StringCollection schemas = new StringCollection();
if (null != include_schemas)
{
schemas.AddRange(include_schemas.Values.ToArray());
}
DateTime pinned = DateTime.UtcNow;
util.disable_console = nouseprogress.HasValue();
ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw);
if (null == serverConnection)
{
return(2);
}
Server server = new Server(serverConnection);
Scripter scripter = new Scripter(server);
ScriptingOptions op = new ScriptingOptions
{
AllowSystemObjects = false
, WithDependencies = true
};
scripter.Options = op;
UrnCollection urns = new UrnCollection();
List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> preobjects = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>();
Console.WriteLine("CONNECTED ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
//bool display_progress = (!useprogress.HasValue()) && System.Console.h
bool fast = querymode.HasValue();
Database db = server.Databases[sqldb.Value()];
//add all or just one sp
if (one_stored.HasValue())
{
var sp = db.StoredProcedures[one_stored.Value()];
urns.Add(sp.Urn);
}
else
{
SchemaCollection sc = db.Schemas;
foreach (Schema schema in sc)
{
if (!schema.IsSystemObject)
{
//if (null==schemas || schemas.Count==0 || (null != schemas && schemas.Count != 0 && schemas.Contains(schema.Name)))
preobjects.Add(schema.Urn);
}
}
TableCollection tc = db.Tables;
add_urns_from_collection(tc, urns, (!nouseprogress.HasValue()));
if (fast)
{
add_urn_from_query(db, "P", (sp, sch) => db.StoredProcedures[sp, sch].Urn, urns, (!nouseprogress.HasValue())
, (sp, sch) => !db.StoredProcedures[sp, sch].IsSystemObject);
}
else
{
var sp = server.Databases[sqldb.Value()].StoredProcedures;
add_urns_from_collection(sp, urns);
}
//--------------------------------
if (fast)
{
add_urn_from_query(db, "V", (sp, sch) => db.Views[sp, sch].Urn, urns, (!nouseprogress.HasValue()));
}
else
{
var vs = server.Databases[sqldb.Value()].Views;
add_urns_from_collection(vs, urns);
}
var ss = server.Databases[sqldb.Value()].Synonyms;
add_urns_from_collection(ss, urns);
if (fast)
{
add_urn_from_query(db, "IF", (sp, sch) => db.UserDefinedFunctions[sp, sch].Urn, urns, (!nouseprogress.HasValue()));
}
else
{
var ff = server.Databases[sqldb.Value()].UserDefinedFunctions;
add_urns_from_collection(ff, urns);
}
var ut = server.Databases[sqldb.Value()].UserDefinedDataTypes;
add_urns_from_collection(ut, urns);
var tt = server.Databases[sqldb.Value()].UserDefinedTypes;
add_urns_from_collection(tt, urns);
var dt = server.Databases[sqldb.Value()].UserDefinedTableTypes;
add_urns_from_collection(dt, urns);
}
//string s = urns[0].GetAttribute("Schema");
//(?m)(?<=\@Schema=)'(.+?)'
Console.WriteLine("DISCOVERING ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
//scripter.DiscoveryProgress += Scripter_DiscoveryProgress;
DependencyTree tr = scripter.DiscoverDependencies(urns, true);
Console.WriteLine("DEPENDENCY ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
DependencyCollection dc = scripter.WalkDependencies(tr);
Console.WriteLine("WALKED ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
dependency_index index = dependency.index(tr);
Console.WriteLine("INDEXED ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
string path = indexfile.Value();
if (null != path)
{
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
System.IO.File.AppendAllText(path, "#file auto-generated" + Environment.NewLine);
}
foreach (Microsoft.SqlServer.Management.Sdk.Sfc.Urn urn in preobjects)
{
if (schemas == null || schemas.Count == 0 || (schemas.Count > 0 && schemas.IndexOf(GetSchemaFromUrn(urn.ToString())) != -1))
{
UrnToIndex(db.Name, path, urn, index);
}
}
foreach (DependencyCollectionNode j in dc)
{
Microsoft.SqlServer.Management.Sdk.Sfc.Urn urn = j.Urn;
if (schemas == null || schemas.Count == 0 || (schemas.Count > 0 && schemas.IndexOf(GetSchemaFromUrn(urn.ToString())) != -1))
{
UrnToIndex(db.Name, path, urn, index);
}
}
Console.WriteLine("EXPORTED ({0})", DateTime.UtcNow.Subtract(pinned));
return(0);
});
});
commandLineApplication.Command("urn", command =>
{
var urn = command.Option("-u | --urn", "Sql Urn", CommandOptionType.SingleValue);
command.Description = @"Normalize an Input.
From Server[@Name='4f4c6527222b']/Database[@Name='MONITORING']/Table[@Name='Procedures' and @Schema='Gathering']
to Table:[Gathering].[Procedures]";
command.OnExecute(() => {
Console.WriteLine(NormalizeUrn(urn.Value()));
return(0);
});
});
commandLineApplication.Command("script", command =>
{
command.Options.AddRange(command.Parent.Options);
command.Description = $"{command.Name} allows to script objects listed in a file or in the command line";
var target = command.Option("-t | --target", "Sql target Object. For instance Table:[dbo].[Table_1]", CommandOptionType.MultipleValue);
var output = command.Option("-o | --output", "Scripts Directory Output", CommandOptionType.SingleValue);
var file = command.Option("-f | -i | --file", "Input File", CommandOptionType.SingleValue);
var version = command.Option("--sql-version", "Sql Version Generation Target", CommandOptionType.SingleValue);
var file_version = command.Option("--file-version", "Enable object version support", CommandOptionType.NoValue);
var modified = command.Option("--modified", "Export all object modified in the last <input> minutes. Es 1440 last day", CommandOptionType.SingleValue);
var plural_exceptions = command.Option("-sp | --singular-plural", "Singular|Plural exceptions i.e. Security|Security", CommandOptionType.MultipleValue);
command.OnExecute(() =>
{
util.disable_console = nouseprogress.HasValue();
StringCollection pluralExceptions = new StringCollection();
if (null != plural_exceptions)
{
pluralExceptions.AddRange(plural_exceptions.Values.ToArray());
}
ProcessPlurals(pluralExceptions);
ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw);
if (null == serverConnection)
{
return(2);
}
Server server = new Server(serverConnection);
Database db = server.Databases[sqldb.Value()];
//TODO: ALLOW MULTIPLE TARGETS AND MULTIPLE FILES
List <string> objs = new List <string>();
objs.AddRange(target.Values.ToArray());
if (null != file.Value())
{
string [] lines = System.IO.File.ReadAllLines(file.Value());
objs.AddRange(lines);
}
if (modified.HasValue())
{
int minutes = int.Parse(modified.Value());
string [] mods = exporter.get_modified_objects(db, minutes);
foreach (string obj in mods)
{
Console.WriteLine(string.Format("\t\tMODIFIED:\t{0}", obj));
}
objs.AddRange(mods);
}
string outputdir = output.Value() ?? "./";
SqlServerVersion sql_version = SqlServerVersion.Version100;
if (version.HasValue())
{
sql_version = (SqlServerVersion)Enum.Parse(typeof(SqlServerVersion), version.Value());
}
scripter.Script(objs.ToArray(), db
, outputdir, (!nouseprogress.HasValue())
, sql_version
, file_version.HasValue());
return(0);
});
//scripter.Script(
});
commandLineApplication.Command("build", command =>
{
command.Options.AddRange(command.Parent.Options);
var indexfiles = command.Option("-i | --index", "Input Index File", CommandOptionType.MultipleValue);
var excludetyes = command.Option("-x | --exclude-types", "Types to exclude from the index", CommandOptionType.MultipleValue);
var output = command.Option("-o | --output", "Script Build Output", CommandOptionType.SingleValue);
var basepath = command.Option("-b | --basepath", "Root of files referenced by index", CommandOptionType.SingleValue);
var database_version = command.Option("--database-version", "Insert database version in script with object version", CommandOptionType.SingleValue);
var use_relative_path = command.Option("-r | --relative-path", "Use indexes relative path to reference files", CommandOptionType.NoValue);
var plural_exceptions = command.Option("-sp | --singular-plural", "Singular|Plural exceptions i.e. Security|Security", CommandOptionType.MultipleValue);
command.OnExecute(() =>
{
string outputfile = output.Value();
if (null != outputfile)
{
if (System.IO.File.Exists(outputfile))
{
System.IO.File.Delete(outputfile);
}
}
StringCollection pluralExceptions = new StringCollection();
if (null != plural_exceptions)
{
pluralExceptions.AddRange(plural_exceptions.Values.ToArray());
}
ProcessPlurals(pluralExceptions);
//ProcessDirs(pretypes.Values.ToArray(), outputfile);
bool relative_path = use_relative_path.HasValue();
string basep = basepath.Value();
string main_index = indexfiles.Values[0];
if (null == basep)
{
basep = System.IO.Path.GetDirectoryName(main_index);
}
foreach (string indexfile in indexfiles.Values)
{
string indexfilepath = System.IO.Path.GetFullPath(System.IO.Path.Join(basep, System.IO.Path.GetFileName(indexfile)));
if (!System.IO.File.Exists(indexfilepath))
{
indexfilepath = System.IO.Path.GetFullPath(indexfile);
}
string indexfiledir = System.IO.Path.GetDirectoryName(indexfile);
System.Console.WriteLine("Adding " + System.IO.Path.GetFileName(indexfile));
string[] types = System.IO.File.ReadAllLines(relative_path ? indexfile : indexfilepath);
int types_count = 0;
foreach (string tt in types)
{
obj_info oi = util.ObjectInfo(tt);
util.drawTextProgressBar(++types_count, types.Length, $" ({tt}) ");
if (oi.is_type)
{
if (!excludetyes.Values.Contains(oi.type))
{
string source = util.FilePath((relative_path ? indexfiledir : basep), oi, false);
string content = System.IO.File.ReadAllText(source);
if (database_version.HasValue())
{
content = scripter.insert_database_version(content, database_version.Value());
}
if (null != outputfile)
{
System.IO.File.AppendAllText(outputfile, content);
}
else
{
Console.Write(content);
}
}
}
}
}
//ProcessDirs(posttypes.Values.ToArray(), outputfile);
});
});
commandLineApplication.Command("coverage", command =>
{
command.Options.AddRange(command.Parent.Options);
command.Description = @"Run sql stetament from files or command line and track coverage";
var indexfiles = command.Option("-i | --input", "Input Coverage File", CommandOptionType.MultipleValue);
var statements = command.Option("-s | --statement", "Input Coverage Statement", CommandOptionType.MultipleValue);
var free_proccache = command.Option("-f | --free-proccache", @"Run DBCC FREEPROCCACHE before your test in order
to count only what you are running and not previous runs.
Do Not use in a production system.", CommandOptionType.NoValue);
var no_exec = command.Option("-n | --no-exec", @"Do not Run the procedure.", CommandOptionType.NoValue);
var tout_exec = command.Option("-t | --timeout-exec", @"Sql executions timeout.", CommandOptionType.SingleValue);
var datail = command.Option("--detail", @"Provide the list of not covered query_hash", CommandOptionType.NoValue);
var save = command.Option("--save", @"save a test result with performance and coverage", CommandOptionType.SingleValue);
command.OnExecute(() =>
{
util.disable_console = nouseprogress.HasValue();
ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw);
if (null == serverConnection)
{
return(2);
}
Server server = new Server(serverConnection);
Database db = server.Databases[sqldb.Value()];
if (null == db)
{
throw new ScripterException("Invalid database");
}
string save_path = null;
if (save.HasValue())
{
save_path = save.Value();
}
if (free_proccache.HasValue())
{
db.ExecuteNonQuery("DBCC FREEPROCCACHE");
}
int timeout = -1;
if (tout_exec.HasValue())
{
timeout = Convert.ToInt32(tout_exec.Value());
}
foreach (string statement in statements.Values)
{
string sql = statement;
handle_coverage(db, sql, !no_exec.HasValue(), datail.HasValue(), save_path, timeout);
}
foreach (string indexfile in indexfiles.Values)
{
string[] lines = System.IO.File.ReadAllLines(indexfile);
string sql = string.Join("\r\n", lines);
handle_coverage(db, sql, !no_exec.HasValue(), datail.HasValue(), save_path, timeout);
}
return(0);
});
});
commandLineApplication.Command("template", command =>
{
command.Options.AddRange(command.Parent.Options);
command.Description = @"Run sql statement from files or command line and track coverage";
//var indexfiles = command.Option("-i | --input", "Input Coverage File", CommandOptionType.MultipleValue);
//var statements = command.Option("-s | --statement", "Input Coverage Statement", CommandOptionType.MultipleValue);
//var free_proccache = command.Option("-f | --free-proccache", @"Run DBCC FREEPROCCACHE before your test in order
// to count only what you are running and not previous runs.
// Do Not use in a production system.", CommandOptionType.NoValue);
//var no_exec = command.Option("-n | --no-exec", @"Do not Run the procedure.", CommandOptionType.NoValue);
//var datail = command.Option("--detail", @"Provide the list of not covered query_hash", CommandOptionType.NoValue);
//var save = command.Option("--save", @"save a test result with performance and coverage", CommandOptionType.SingleValue);
var table = command.Option("-t | --table", "The table name to genarate CRUD", CommandOptionType.MultipleValue);
var output = command.Option("-o | --output", "Scripts Directory Output", CommandOptionType.SingleValue);
//var file = command.Option("-f | -i | --file", "Input File", CommandOptionType.SingleValue);
command.OnExecute(() =>
{
//string outputdir = output.Value() ?? "./StoredProcedures";
util.disable_console = nouseprogress.HasValue();
ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw);
if (null == serverConnection)
{
return(2);
}
Server server = new Server(serverConnection);
Database db = server.Databases[sqldb.Value()];
if (null == db)
{
throw new ScripterException("Invalid database");
}
foreach (string t in table.Values)
{
Table db_table;
if (!t.Contains("."))
{
db_table = db.Tables[t];
}
else
{
string a = t.Split('.')[0];
string b = t.Split('.')[1];
db_table = db.Tables[b, a];
}
Template temp = new Template();
temp.Table = db_table;
//Console.Write(temp.Execute());
StringCollection sc = temp.Execute();
foreach (string s in sc)
{
Console.Write(s);
db.ExecuteNonQuery(s);
}
}
return(0);
});
});
commandLineApplication.HelpOption("-h | --help", inherited: true);
try
{
int r = commandLineApplication.Execute(args);
return(r);
}
catch (CommandParsingException ex)
{
Console.Error.Write("Invalid Command Line: ");
Console.Error.WriteLine(ex.Message);
Console.Error.WriteLine(commandLineApplication.GetHelpText());
return(22);
}
catch (Exception ex)
{
ConsoleColor color = Console.ForegroundColor;
try{
Console.ForegroundColor = ConsoleColor.Red;
Console.Error.WriteLine(ex.Message);
Console.ForegroundColor = ConsoleColor.Gray;
Console.Error.WriteLine(ex.ToString());
}
finally
{
Console.ForegroundColor = color;
}
return(99);
}
}
private static void GenereateData(Database db, Scripter scripter, StringBuilder sbScript)
{
var tbls = new Table[db.Tables.Count];
db.Tables.CopyTo(tbls, 0);
var tree = scripter.DiscoverDependencies(tbls, true);
var depwalker = new DependencyWalker();
var depcoll = depwalker.WalkDependencies(tree);
var sb = new StringBuilder();
//progressBar1.Maximum = sviews.Count;
foreach (DependencyCollectionNode dep in depcoll)
{
var o = db.Tables[dep.Urn.GetAttribute("Name")];
if (o != null && !o.IsSystemObject)
sb.AppendFormat("EXEC sp_generate_inserts @table_name='{0}', @owner='dbo'{1}", dep.Urn.GetAttribute("Name"), Environment.NewLine);
}
DataSet dset = db.ExecuteWithResults(sb.ToString());
foreach (DataTable dt in dset.Tables)
{
foreach (DataRow dr in dt.Rows)
{
sbScript.AppendLine(dr[0].ToString());
}
}
}
/// <summary>
/// Event handler for where used menu item.
/// Adds a "USED BY" node and performs a dependency discovery after which
/// all dependents are added to the "USED BY" node.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void WhereUsedToolStripMenuItem_Click(object sender, EventArgs e)
{
TreeNode node, newNode;
node = DependenciesTreeView.SelectedNode;
if (node.Nodes.ContainsKey(Properties.Resources.UsedBy)
|| node.Name == Properties.Resources.UsedBy)
{
return;
}
newNode = new TreeNode(Properties.Resources.UsedBy);
newNode.Name = Properties.Resources.UsedBy;
node.Nodes.Add(newNode);
// Advanced Scripting
Scripter scripter = new Scripter(this.sqlServerSelection);
Urn[] urns = new Urn[1];
urns[0] = (Urn)node.Tag;
// Discover dependents
DependencyTree tree = scripter.DiscoverDependencies(urns, DependencyType.Children);
// Add to tree (recursive)
AddChildren(newNode, tree.FirstChild);
node.Expand();
newNode.Expand();
}
private void ShowDependenciesmenuItem_Click(object sender, EventArgs e)
{
Table table;
DependencyTree deps;
Scripter scripter;
SqlSmoObject[] smoObjects = new SqlSmoObject[1];
DependencyForm form = new DependencyForm();
// Just make sure something is selected
if (this.TableListView.SelectedIndices.Count == 0)
{
return;
}
// It's the first one as we only allow one selection
table = (Table)(this.TableListView.SelectedItems[0].Tag);
// Declare and instantiate new Scripter object
scripter = new Scripter(table.Parent.Parent);
// Declare array of SqlSmoObjects
smoObjects[0] = table;
// Discover dependencies
deps = scripter.DiscoverDependencies(smoObjects, true);
// Show dependencies
form.ShowDependencies(table.Parent.Parent, deps);
form.Show();
}
static int Main(string[] args)
{
Console.OutputEncoding = System.Text.Encoding.UTF8;
var commandLineApplication = new CommandLineApplication();
commandLineApplication.Name = "sqlscripter";
commandLineApplication.Description = "Sqlscripter";
var sqlserver = commandLineApplication.Option("-S | --server", "Sql Server", CommandOptionType.SingleValue);
var sqluser = commandLineApplication.Option("-U | --user", "Sql User. Do not use in order to switch to integrated authentication.", CommandOptionType.SingleValue);
var sqlpsw = commandLineApplication.Option("-P | --psw", "Sql Password", CommandOptionType.SingleValue);
var sqldb = commandLineApplication.Option("-d | --database", "Sql Database", CommandOptionType.SingleValue);
var nouseprogress = commandLineApplication.Option("--no-progress", "Disable progress bar", CommandOptionType.NoValue);
commandLineApplication.Command("info", command =>
{
command.Options.AddRange(command.Parent.Options);
command.Description = $"{command.Name} render server information";
command.OnExecute(() =>
{
ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw);
if (null == serverConnection)
{
return(2);
}
Server server = new Server(serverConnection);
System.Console.WriteLine("Databases:");
foreach (var db in server.Databases)
{
System.Console.WriteLine($"\t{db}");
}
return(0);
});
});
commandLineApplication.Command("dbindex", command =>
{
command.Options.AddRange(command.Parent.Options);
command.Description = $"{command.Name} allow to connect to a database and build an ordered index of all objects";
var indexfile = command.Option("-i | --index", "Generate Index File", CommandOptionType.SingleValue);
var querymode = command.Option("--query-mode", "Use object query for objects", CommandOptionType.NoValue);
command.OnExecute(() =>
{
DateTime pinned = DateTime.UtcNow;
disable_console = nouseprogress.HasValue();
ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw);
if (null == serverConnection)
{
return(2);
}
Server server = new Server(serverConnection);
Scripter scripter = new Scripter(server);
ScriptingOptions op = new ScriptingOptions
{
AllowSystemObjects = false
, WithDependencies = true
};
scripter.Options = op;
UrnCollection urns = new UrnCollection();
List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> preobjects = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>();
Console.WriteLine("CONNECTED ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
//bool display_progress = (!useprogress.HasValue()) && System.Console.h
bool fast = querymode.HasValue();
Database db = server.Databases[sqldb.Value()];
//server.GetSmoObject
SchemaCollection sc = db.Schemas;
foreach (Schema schema in sc)
{
if (!schema.IsSystemObject)
{
preobjects.Add(schema.Urn);
}
}
TableCollection tc = db.Tables;
add_urns_from_collection(tc, urns, (!nouseprogress.HasValue()));
if (fast)
{
add_urn_from_query(db, "P", (sp, sch) => db.StoredProcedures[sp, sch].Urn, urns, (!nouseprogress.HasValue()));
}
else
{
var sp = server.Databases[sqldb.Value()].StoredProcedures;
add_urns_from_collection(sp, urns);
}
//--------------------------------
if (fast)
{
add_urn_from_query(db, "V", (sp, sch) => db.Views[sp, sch].Urn, urns, (!nouseprogress.HasValue()));
}
else
{
var vs = server.Databases[sqldb.Value()].Views;
add_urns_from_collection(vs, urns);
}
var ss = server.Databases[sqldb.Value()].Synonyms;
add_urns_from_collection(ss, urns);
if (fast)
{
add_urn_from_query(db, "IF", (sp, sch) => db.UserDefinedFunctions[sp, sch].Urn, urns, (!nouseprogress.HasValue()));
}
else
{
var ff = server.Databases[sqldb.Value()].UserDefinedFunctions;
add_urns_from_collection(ff, urns);
}
var tt = server.Databases[sqldb.Value()].UserDefinedTypes;
add_urns_from_collection(tt, urns);
Console.WriteLine("DISCOVERING ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
//scripter.DiscoveryProgress += Scripter_DiscoveryProgress;
DependencyTree tr = scripter.DiscoverDependencies(urns, true);
Console.WriteLine("DEPENDENCY ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
DependencyCollection dc = scripter.WalkDependencies(tr);
Console.WriteLine("WALKED ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
dependency_index index = dependency.index(tr);
Console.WriteLine("INDEXED ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
string path = indexfile.Value();
if (null != path)
{
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
System.IO.File.AppendAllText(path, "#file auto-generated" + Environment.NewLine);
}
foreach (Microsoft.SqlServer.Management.Sdk.Sfc.Urn urn in preobjects)
{
UrnToIndex(db.Name, path, urn, index);
}
foreach (DependencyCollectionNode j in dc)
{
Microsoft.SqlServer.Management.Sdk.Sfc.Urn urn = j.Urn;
UrnToIndex(db.Name, path, urn, index);
}
Console.WriteLine("EXPORTED ({0})", DateTime.UtcNow.Subtract(pinned));
return(0);
});
});
commandLineApplication.Command("urn", command =>
{
var urn = command.Option("-u | --urn", "Sql Urn", CommandOptionType.SingleValue);
command.Description = @"Normalize an Input.
From Server[@Name='4f4c6527222b']/Database[@Name='MONITORING']/Table[@Name='Procedures' and @Schema='Gathering']
to Table:[Gathering].[Procedures]";
command.OnExecute(() => {
Console.WriteLine(NormalizeUrn(urn.Value()));
return(0);
});
});
commandLineApplication.Command("script", command =>
{
command.Options.AddRange(command.Parent.Options);
command.Description = $"{command.Name} allows to script objects listed in a file or in the command line";
var target = command.Option("-t | --target", "Sql target Object", CommandOptionType.MultipleValue);
var output = command.Option("-o | --output", "Script Output", CommandOptionType.SingleValue);
var file = command.Option("-f | -i | --file", "Input File", CommandOptionType.SingleValue);
command.OnExecute(() =>
{
disable_console = nouseprogress.HasValue();
ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw);
if (null == serverConnection)
{
return(2);
}
Server server = new Server(serverConnection);
string[] objs = target.Values.ToArray();
if (null != file.Value())
{
objs = System.IO.File.ReadAllLines(file.Value());
}
string outputdir = output.Value() ?? "./";
Script(objs, server.Databases[sqldb.Value()], outputdir, (!nouseprogress.HasValue()));
return(0);
});
//scripter.Script(
});
commandLineApplication.Command("build", command =>
{
command.Options.AddRange(command.Parent.Options);
var indexfiles = command.Option("-i | --index", "Input Index File", CommandOptionType.MultipleValue);
var excludetyes = command.Option("-x | --exclude-types", "Types to exclude from the index", CommandOptionType.MultipleValue);
var output = command.Option("-o | --output", "Script Build Output", CommandOptionType.SingleValue);
var basepath = command.Option("-b | --basepath", "Root of files referenced by index", CommandOptionType.SingleValue);
command.OnExecute(() =>
{
string outputfile = output.Value();
if (null != outputfile)
{
if (System.IO.File.Exists(outputfile))
{
System.IO.File.Delete(outputfile);
}
}
//ProcessDirs(pretypes.Values.ToArray(), outputfile);
string basep = basepath.Value();
string main_index = indexfiles.Values[0];
if (null == basep)
{
basep = System.IO.Path.GetDirectoryName(main_index);
}
foreach (string indexfile in indexfiles.Values)
{
string indexfilepath = System.IO.Path.GetFullPath(indexfile);
System.Console.WriteLine("Adding " + System.IO.Path.GetFileName(indexfile));
string[] types = System.IO.File.ReadAllLines(indexfilepath);
int types_count = 0;
foreach (string tt in types)
{
obj_info oi = ObjectInfo(tt);
drawTextProgressBar(++types_count, types.Length, $" ({tt}) ");
if (oi.is_type)
{
if (!excludetyes.Values.Contains(oi.type))
{
string source = FilePath(basep, oi, false);
string content = System.IO.File.ReadAllText(source);
if (null != outputfile)
{
System.IO.File.AppendAllText(outputfile, content);
}
else
{
Console.Write(content);
}
}
}
}
}
//ProcessDirs(posttypes.Values.ToArray(), outputfile);
});
});
commandLineApplication.Command("coverage", command =>
{
command.Options.AddRange(command.Parent.Options);
command.Description = @"Run sql stetament from files or command line and track coverage";
var indexfiles = command.Option("-i | --input", "Input Coverage File", CommandOptionType.MultipleValue);
var statements = command.Option("-s | --statement", "Input Coverage Statement", CommandOptionType.MultipleValue);
var free_proccache = command.Option("-f | --free-proccache", @"Run DBCC FREEPROCCACHE before your test in order
to count only what you are running and not previous runs.
Do Not use in a production system.", CommandOptionType.NoValue);
command.OnExecute(() =>
{
disable_console = nouseprogress.HasValue();
ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw);
if (null == serverConnection)
{
return(2);
}
Server server = new Server(serverConnection);
Database db = server.Databases[sqldb.Value()];
if (free_proccache.HasValue())
{
db.ExecuteNonQuery("DBCC FREEPROCCACHE");
}
foreach (string statement in statements.Values)
{
string sql = statement;
handle_coverage(db, sql);
}
foreach (string indexfile in indexfiles.Values)
{
string[] lines = System.IO.File.ReadAllLines(indexfile);
string sql = string.Join("\r\n", lines);
handle_coverage(db, sql);
}
return(0);
});
});
commandLineApplication.HelpOption("-h | --help", inherited: true);
try
{
int r = commandLineApplication.Execute(args);
return(r);
}
catch (CommandParsingException ex)
{
Console.Error.Write("Invalid Command Line: ");
Console.Error.WriteLine(ex.Message);
Console.Error.WriteLine(commandLineApplication.GetHelpText());
return(22);
}
catch (Exception ex)
{
Console.Error.WriteLine(ex.ToString());
return(99);
}
}
private void dependenciesToolStripMenuItem_Click(object sender, EventArgs e)
{
if (this.ListView.SelectedItems.Count == 0)
{
return;
}
object selectedObject = this.ListView.SelectedItems[0].Tag;
IScriptable scriptableObject = selectedObject as IScriptable;
if (scriptableObject != null
&& !(selectedObject is Database))
{
SqlSmoObject[] smoObjects = new SqlSmoObject[1];
smoObjects[0] = (SqlSmoObject)selectedObject;
Scripter scripter = new Scripter(this.sqlServerSelection);
DependenciesForm frm = new DependenciesForm(this.sqlServerSelection,
scripter.DiscoverDependencies(smoObjects, DependencyType.Parents));
frm.ShowDialog(this);
}
}
private void DependenciesMenuItem_Click(object sender, EventArgs e)
{
Urn[] urns = new Urn[1];
Scripter scripter;
DependencyForm frm;
DependencyTree deps;
// Get the urn from the node
urns[0] = (Urn)(this.DependenciesTreeView.SelectedNode.Tag);
// Instantiate scripter
scripter = new Scripter(server);
// Get a new form
frm = new DependencyForm();
// Discover dependencies
deps = scripter.DiscoverDependencies(urns, DependencyType.Parents);
// Set the tree
frm.ShowDependencies(server, deps);
// Show the form
frm.Show();
}