public static string FilePath(string output, obj_info oi, bool dooutput = true)
{
string prefix = GetPlural(oi.type);
if (string.IsNullOrEmpty(prefix))
{
prefix = oi.type + "s";
}
if (_pluralExceptionsTable != null && _pluralExceptionsTable.Count > 0 && _pluralExceptionsTable[oi.type.ToLower()] != null)
{
prefix = _pluralExceptionsTable[oi.type.ToLower()];
}
string dir = System.IO.Path.GetFullPath(
System.IO.Path.Combine(output, prefix)
);
if (dooutput)
{
System.IO.Directory.CreateDirectory(dir);
}
return(System.IO.Path.Combine(dir, $"{oi.schema}.{oi.name}.sql"));
//return file;
}
private static void check_oi(SqlSmoObject obj, obj_info oi)
{
if (null == obj)
{
throw new ScripterException(
string.Format("cannot find {0}: {2} {1}", oi.type, oi.name, oi.schema)
);
}
}
//private static
private static void UrnToIndex(string target_db
, string path
, Microsoft.SqlServer.Management.Sdk.Sfc.Urn urn
, dependency_index index)
{
string regex = $"Database\\[@Name='{target_db}";
bool trouble = false;
string output_line = "";
if (!System.Text.RegularExpressions.Regex.IsMatch(urn.Value, regex
, System.Text.RegularExpressions.RegexOptions.IgnoreCase))
{
output_line = string.Format("##External-Entity-Database\t{0}", urn.Value);
trouble = true;
}
else
{
string str_info = NormalizeUrn(urn);
obj_info info = util.ObjectInfo(str_info);
output_line = str_info;
if ("UnresolvedEntity" == info.type)
{
output_line = string.Format("##UnresolvedEntity\t{0}\t{1}", urn, str_info);
trouble = true;
}
}
if (trouble)
{
string[] parents = index.get_parents(urn.Value);
if (null == parents)
{
output_line += "\t->NO-PARENTS";
}
else
{
output_line = string.Format("{1}\t->PARENTS:\t{0}", string.Join('\t', parents), output_line);
}
}
if (null != path)
{
System.IO.File.AppendAllText(path, output_line + Environment.NewLine);
}
else
{
Console.WriteLine(output_line);
}
}
private static string get_object_version(Database db, obj_info oi)
{
string sql = get_listextendedproperty(FILE_VERSION, oi);
if (null != sql)
{
DataSet ds = db.ExecuteWithResults(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return(ds.Tables[0].Rows[0][0].ToString());
}
}
return(null);
}
//IF EXISTS (SELECT value FROM fn_listextendedproperty('FILE VERSION', 'schema', 'dbo', 'TABLE', 'IDX_HISTORY', NULL, NULL))
private static string get_listextendedproperty(string property, obj_info oi)
{
//AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE
//, RULE, SEQUENCE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, XML SCHEMA COLLECTION, and NULL.
//db.ExecuteWithResults
string type = extended_type(oi);
if (null != type)
{
string sql = $"SELECT value FROM fn_listextendedproperty('{property}', 'schema', '{oi.schema}', '{type}', '{oi.name}', NULL, NULL)";
return(sql);
}
return(null);
}
private static string FilePath(string output, obj_info oi, bool dooutput = true)
{
string prefix = oi.type + "s";
string dir = System.IO.Path.GetFullPath(
System.IO.Path.Combine(output, prefix)
);
if (dooutput)
{
System.IO.Directory.CreateDirectory(dir);
}
return(System.IO.Path.Combine(dir, $"{oi.schema}.{oi.name}.sql"));
//return file;
}
static obj_info ObjectInfo(string obj)
{
obj_info r = null;
if (string.IsNullOrEmpty(obj))
{
r = new obj_info()
{
type = "null"
};
}
if (obj.StartsWith("#"))
{
r = new obj_info()
{
type = "comment"
};
}
if (null == r)
{
string rx = @"([^:]+):\[([^\]]*)\]\.\[([^\]]+)\]";
if (!System.Text.RegularExpressions.Regex.IsMatch(obj, rx))
{
throw new ScripterException($"Invalid Object Name used: {obj} does not match {rx}");
}
var m = System.Text.RegularExpressions.Regex.Match(obj, rx);
r = new obj_info()
{
type = m.Groups[1].Value
, name = m.Groups[3].Value
, schema = m.Groups[2].Value
, is_type = true
};
}
return(r);
}
private static string extended_type(obj_info oi)
{
string type = null;
if ("Table" == oi.type)
{
type = "TABLE";
}
if ("StoredProcedure" == oi.type)
{
type = "PROCEDURE";
}
if ("View" == oi.type)
{
type = "VIEW";
}
if ("Synonym" == oi.type)
{
//type = "SYNONYM";
type = null;
}
if ("UserDefinedFunction" == oi.type)
{
type = "FUNCTION";
}
if (("UserDefinedType" == oi.type) || ("UserDefinedDataType" == oi.type) || ("UserDefinedTableType" == oi.type))
{
type = "TYPE";
}
return(type);
}
private static void Script(string[] target, Database db
, Scripter scripter, string output, bool progress)
{
SqlSmoObject[] objs = new SqlSmoObject[1];
int count = target.Length;
int jdx = 0;
foreach (string obname in target)
{
obj_info oi = util.ObjectInfo(obname);
scripter.Options.IncludeIfNotExists = true;
scripter.Options.ScriptForCreateDrop = false;
if ("null" == oi.type || "comment" == oi.type)
{
jdx++;
continue;
}
string file = null;
string prefix = "";
if (null != output)
{
file = util.FilePath(output, oi);
//if (System.IO.File.Exists(file))
//System.IO.File.Delete(file);
}
string version = null;
if (_do_version)
{
version = get_object_version(db, oi);
if (null == version || (!is_version_valid(version)))
{
version = "0.0.0.0";
}
if (null != file && System.IO.File.Exists(file))
{
string sql_file = System.IO.File.ReadAllText(file);
string file_version = get_extended_version(sql_file);
if (null != file_version && is_version_valid(file_version) && 0 > version.CompareTo(file_version))
{
version = file_version;
}
}
}
if ("Table" == oi.type)
{
scripter.Options.DriDefaults = true;
objs[0] = db.Tables[oi.name, oi.schema];
check_oi(objs[0], oi);
}
if ("StoredProcedure" == oi.type)
{
objs[0] = db.StoredProcedures[oi.name, oi.schema];
check_oi(objs[0], oi);
prefix = ScriptDrop(scripter, objs);
}
if ("View" == oi.type)
{
objs[0] = db.Views[oi.name, oi.schema];
check_oi(objs[0], oi);
prefix = ScriptDrop(scripter, objs);
}
if ("Synonym" == oi.type)
{
objs[0] = db.Synonyms[oi.name, oi.schema];
//check_oi(objs[0], oi);
//prefix = ScriptDrop(scripter, objs);
}
if ("UserDefinedFunction" == oi.type)
{
objs[0] = db.UserDefinedFunctions[oi.name, oi.schema];
check_oi(objs[0], oi);
prefix = ScriptDrop(scripter, objs);
}
if ("UserDefinedTableType" == oi.type)
{
objs[0] = db.UserDefinedTableTypes[oi.name, oi.schema];
check_oi(objs[0], oi);
}
if ("UserDefinedType" == oi.type)
{
objs[0] = db.UserDefinedTypes[oi.name, oi.schema];
check_oi(objs[0], oi);
}
if ("UserDefinedDataType" == oi.type)
{
objs[0] = db.UserDefinedDataTypes[oi.name, oi.schema];
check_oi(objs[0], oi);
}
if ("Schema" == oi.type)
{
objs[0] = db.Schemas[oi.name];
check_oi(objs[0], oi);
}
if (null == objs[0])
{
throw new ScripterException(string.Format("Invalid type: {0} {1}", oi.type, obname));
}
//DependencyTree tr = scripter.DiscoverDependencies(objs, true);
//DependencyCollection dc = scripter.WalkDependencies(tr)
if (null != output && progress)
{
util.drawTextProgressBar(++jdx, count, obname);
}
Script(scripter, objs, file, prefix, version, oi);
}
}
private static string Script(Scripter scripter
, SqlSmoObject[] objs
, string file = null
, string prefix = ""
, string version = null
, obj_info oi = null)
{
StringCollection sqls = scripter.Script(objs);
string sql_return = "";
for (int idx = 0; idx < sqls.Count; idx++)
{
string sql = sqls[idx];
if (sql == "SET ANSI_NULLS ON" ||
sql == "SET QUOTED_IDENTIFIER ON"
)
{
continue;
}
string defaultrx = @"IF NOT EXISTS \(SELECT \* FROM sys.objects WHERE object_id = OBJECT_ID\(N'\[([^\]]+)\]\.\[([^\]]+)\]'\) AND type = 'D'\)";
if (System.Text.RegularExpressions.Regex.IsMatch(sql, defaultrx))
{
sql = fixdefault(sql, defaultrx);
}
sql = prefix + sql;
sql += Environment.NewLine + "GO" + Environment.NewLine;
sql_return = sql_return + sql;
prefix = "";
}
string baseline = "----";
if (null != version)
{
if (!is_version_valid(version))
{
throw new ScripterException("invalid version " + version);
}
if (null == oi)
{
throw new ScripterException("version must specify oi");
}
if (file != null)
{
if (System.IO.File.Exists(file))
{
baseline = System.IO.File.ReadAllText(file);
}
string baseline_clean = clean_up_version(baseline);
if (baseline_clean != sql_return)
{
string[] semver = version.Split(".");
semver[2] = (Int16.Parse(semver[2]) + 1).ToString();
version = $"{semver[0]}.{semver[1]}.{semver[2]}.0";
}
}
string type = extended_type(oi);
if (null != type)
{
string extprop = compile_extended_template(FILE_VERSION, version, oi.schema, type, oi.name);
extprop += compile_extended_template(DATABASE_VERSION, "0.0.0.0", oi.schema, type, oi.name);
if ("TABLE" == type)
{
string ext = get_listextendedproperty(FILE_VERSION, oi);
/*
* string drop = extprop.Replace("sys.sp_addextendedproperty", "sys.sp_dropextendedproperty");
* drop = drop.Replace("GO", "");
*
* drop = System.Text.RegularExpressions.Regex.Replace(drop, @"@value=N'(\d+\.){3}\d+' ,", "");
*/
extprop = $"IF NOT EXISTS({ext}){Environment.NewLine}BEGIN{Environment.NewLine}{extprop.Replace("GO", "")}{Environment.NewLine}END{Environment.NewLine}GO{Environment.NewLine}";
}
sql_return += extprop;
}
}
if (file != null && baseline != sql_return)
{
if (System.IO.File.Exists(file))
{
System.IO.File.Delete(file);
}
//System.IO.File.AppendAllText(file, Environment.NewLine + "GO" + Environment.NewLine);
//System.IO.File.AppendAllText(file, sql);
System.IO.File.WriteAllText(file, sql_return);
}
return(sql_return);
}
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 Script(string[] target, Database db
, Scripter scripter, string output, bool progress)
{
SqlSmoObject[] objs = new SqlSmoObject[1];
int count = target.Length;
int jdx = 0;
foreach (string obname in target)
{
obj_info oi = ObjectInfo(obname);
scripter.Options.IncludeIfNotExists = true;
scripter.Options.ScriptForCreateDrop = false;
if ("null" == oi.type || "comment" == oi.type)
{
jdx++;
continue;
}
string file = null;
if (null != output)
{
file = FilePath(output, oi);
if (System.IO.File.Exists(file))
{
System.IO.File.Delete(file);
}
}
if ("Table" == oi.type)
{
scripter.Options.DriDefaults = true;
objs[0] = db.Tables[oi.name, oi.schema];
}
if ("StoredProcedure" == oi.type)
{
objs[0] = db.StoredProcedures[oi.name, oi.schema];
ScriptDrop(scripter, objs, file);
}
if ("View" == oi.type)
{
objs[0] = db.Views[oi.name, oi.schema];
ScriptDrop(scripter, objs, file);
}
if ("Synonym" == oi.type)
{
objs[0] = db.Synonyms[oi.name, oi.schema];
}
if ("UserDefinedFunction" == oi.type)
{
objs[0] = db.UserDefinedFunctions[oi.name, oi.schema];
ScriptDrop(scripter, objs, file);
}
if ("UserDefinedType" == oi.type)
{
objs[0] = db.UserDefinedTypes[oi.name, oi.schema];
}
if ("Schema" == oi.type)
{
objs[0] = db.Schemas[oi.name];
}
if (null == objs[0])
{
throw new ScripterException(string.Format("Invalid type: {0} {1}", oi.type, obname));
}
//DependencyTree tr = scripter.DiscoverDependencies(objs, true);
//DependencyCollection dc = scripter.WalkDependencies(tr)
if (null != output && progress)
{
drawTextProgressBar(++jdx, count, obname);
}
Script(scripter, objs, file);
}
}
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);
}
}
