public override string OpenTable(string database, string table)
{
string message = string.Empty;
using (SqlConnection connection = new SqlConnection(this.DbConnectionStringBuilder.ConnectionString))
{
Server server = new Server(new ServerConnection(connection));
server.ConnectionContext.Connect();
Table temp = server.Databases[database].Tables[table];
//初始化Scripter
Scripter a = new Scripter(server);
a.Options.Add(ScriptOption.DriAllConstraints);
a.Options.Add(ScriptOption.DriAllKeys);
a.Options.Add(ScriptOption.Default);
a.Options.Add(ScriptOption.ContinueScriptingOnError);
a.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);
a.Options.Add(ScriptOption.IncludeIfNotExists);
UrnCollection collection = new UrnCollection();
collection.Add(temp.Urn);
var sqls = a.Script(collection);
foreach (var s in sqls)
{
message += s;
}
}
return(message);
}
// TODO fix this
private static void TruncateTblUnderlyingView(View v, Server Srvr, Database Db)
{
DependencyWalker Dw = new DependencyWalker(Srvr);
UrnCollection list = new UrnCollection();
list.Add(v.Urn);
List <string> Urns = new List <string>();
DependencyTreeNode node = Dw.DiscoverDependencies(list, true);
DependencyTreeNode child;
if (node.HasChildNodes)
{
child = node.FirstChild;
while (null != child)
{
Urns.Add(child.Urn.XPathExpression[2].ToString());
child = child.NextSibling;
}
}
if (Urns.Count != 1)
{
throw new LoadException(string.Format("Could not truncate the passed view because it does not contain exactly one underlying table: {0}", v.Name));
}
string SchemaName = SchemaFromTableName(Urns[0]);
string ToTruncate = ObjectFromObjName(Urns[0]);
if (Db.Tables.Contains(ToTruncate, SchemaName))
{
Log.InformationMessage("Truncating table: {0} underlying view: {1}", Urns[0], v.Name);
Db.Tables[ToTruncate, SchemaName].TruncateData();
}
}
public static string ScriptIndexes(ServerConnection conn, string tblName)
{
Server srv = new Server(conn);
Database db = srv.Databases[conn.DatabaseName];
Table tbl = db.Tables[tblName];
if (tbl.Indexes.Count == 0)
{
return(String.Empty);
}
Scripter scripter = new Scripter(srv);
scripter.Options.ToFileOnly = false;
UrnCollection urn = new UrnCollection();
for (int i = 0; i < tbl.Indexes.Count; i++)
{
if (tbl.Indexes[i].IndexKeyType == IndexKeyType.DriPrimaryKey)
{
continue;
}
urn.Add(tbl.Indexes[i].Urn);
}
return(CompactStrings(scripter.Script(urn)));
}
private string GenerateScriptSelect(Server server, UrnCollection urns)
{
string script = string.Empty;
ScriptingObject scriptingObject = this.Parameters.ScriptingObjects[0];
Urn objectUrn = urns[0];
string typeName = objectUrn.GetNameForType(scriptingObject.Type);
// select from service broker
if (string.Compare(typeName, "ServiceBroker", StringComparison.CurrentCultureIgnoreCase) == 0)
{
script = Scripter.SelectAllValuesFromTransmissionQueue(objectUrn);
}
// select from queues
else if (string.Compare(typeName, "Queues", StringComparison.CurrentCultureIgnoreCase) == 0 ||
string.Compare(typeName, "SystemQueues", StringComparison.CurrentCultureIgnoreCase) == 0)
{
script = Scripter.SelectAllValues(objectUrn);
}
// select from table or view
else
{
Database db = server.Databases[databaseName];
bool isDw = db.IsSqlDw;
script = new Scripter().SelectFromTableOrView(server, objectUrn, isDw);
}
return(script);
}
private string getTableCreateSql(Server srv, List <Table> tables)
{
Scripter scrp = new Scripter(srv);
scrp.Options.ScriptDrops = false;
scrp.Options.WithDependencies = true;
scrp.Options.Indexes = true;
scrp.Options.DriAllConstraints = true;
scrp.Options.IncludeHeaders = true;
scrp.Options.IncludeIfNotExists = true;
StringBuilder sql = new StringBuilder();
UrnCollection urnCollection = new UrnCollection();
foreach (Table tb in tables)
{
urnCollection.Add(tb.Urn);
}
System.Collections.Specialized.StringCollection sc = scrp.Script(urnCollection);
foreach (string st in sc)
{
sql.AppendFormat("{0}\r\n", st);
}
//sql.Append("--\r\n");
return(sql.ToString());
}
private string getProcedureCreateSql(Server srv, List <StoredProcedure> procedures)
{
StringBuilder sql = new StringBuilder();
// Define a Scripter object and set the required scripting options.
Scripter scrp = new Scripter(srv);
scrp.Options.ScriptDrops = false;
scrp.Options.WithDependencies = true;
scrp.Options.Indexes = true; // To include indexes
scrp.Options.DriAllConstraints = true; // to include referential constraints in the script
scrp.Options.IncludeHeaders = true;
scrp.Options.IncludeIfNotExists = true;
UrnCollection urnCollection = new UrnCollection();
foreach (StoredProcedure procedure in procedures)
{
urnCollection.Add(procedure.Urn);
}
// Iterate through the tables in database and script each one. Display the script.
System.Collections.Specialized.StringCollection sc = scrp.Script(urnCollection);
//sql.AppendFormat("--script for table {0}\r\n", name);
foreach (string st in sc)
{
sql.AppendFormat("{0}\r\n", st);
}
//sql.Append("--\r\n");
return(sql.ToString());
}
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);
}
/// <summary>
/// 生成视图
/// </summary>
/// <param name="views">视图</param>
/// <param name="fileName">文件名路径</param>
/// <returns>是否成功</returns>
public bool GenerateViewScripts(string fileName)
{
try
{
ViewCollection views = _Database.Views;
using (Stream stream = new FileStream(fileName, FileMode.Append, FileAccess.Write, FileShare.None))
{
//声明统一资源名称集合对象
UrnCollection collection = null;
//声明字符串集合对象：存储collection中的所有string对象（在这里其中有3个string对象）
StringCollection sqls = null;
collection = new UrnCollection();
for (int i = 0; i < views.Count; i++)
{
if (views[i].Owner == "dbo")
{
collection.Add(views[i].Urn);
}
else
{
break;
}
}
//collection.Add(table.Urn);
sqls = _Scripter.Script(collection);
//遍历字符串集合对象sqls中的string对象，选择要输出的脚本语句：
if (sqls != null && sqls.Count > 0)
{
//写入文件
byte[] bytes = null;
string temp = "";
foreach (string s in sqls)
{
temp = s + "\r\n";
bytes = Encoding.Default.GetBytes(temp);
stream.Write(bytes, 0, bytes.Length);
}
}
stream.Close();
}
return(true);
}
catch (Exception ex)
{
return(false);
// WriteExceptionLog(ex);
}
}
/// <summary>
/// 获取视图的脚本
/// </summary>
/// <param name="dbName"></param>
/// <param name="vName"></param>
/// <returns></returns>
public string GetViewScript(string dbName, string vName)
{
StringBuilder sb = new StringBuilder();
View view = ser.Databases[dbName].Views[vName];
Scripter script = GetScripter();
UrnCollection urns = new UrnCollection();
urns.Add(view.Urn);
StringCollection sqlScript = script.Script(urns);
for (int i = 0; i < sqlScript.Count; i++)
{
sb.Append(sqlScript[i]);
sb.Append("\r\nGO\r\n");
}
return(sb.ToString());
}
public string getProcedureScript(string dbName, string pName)
{
StringBuilder sb = new StringBuilder();
StoredProcedure procedure = ser.Databases[dbName].StoredProcedures[pName];
Scripter script = GetScripter();
UrnCollection urns = new UrnCollection();
urns.Add(procedure.Urn);
StringCollection sqlScript = script.Script(urns);
foreach (string str in sqlScript)
{
sb.Append(str);
sb.Append("\r\nGO\r\n");
}
return(sb.ToString());
}
public string getFunctionScript(string dbName, string fName)
{
StringBuilder sb = new StringBuilder();
UserDefinedFunction fun = ser.Databases[dbName].UserDefinedFunctions[fName];
Scripter script = GetScripter();
UrnCollection urns = new UrnCollection();
urns.Add(fun.Urn);
StringCollection sqlScript = script.Script(urns);
foreach (string str in sqlScript)
{
sb.Append(str);
sb.Append("\r\nGO\n\r");
}
return(sb.ToString());
}
static void Main(string[] args)
{
using (var connection = new SqlConnection("Data Source=.;Initial Catalog=...;Integrated Security=True"))
{
connection.Open( );
var serverConnection = new ServerConnection(connection);
var server = new Server(serverConnection);
var db = server.Databases["..."];
var objects = new UrnCollection( );
foreach (Table table in db.Tables)
{
objects.Add(table.Urn);
}
var dependency = new DependencyWalker(server);
var tree = dependency.DiscoverDependencies(objects, DependencyType.Parents);
Walk(tree.FirstChild);
}
}
/// <summary>
/// 获取表的脚本
/// </summary>
/// <param name="dbName"></param>
/// <param name="tName"></param>
/// <returns></returns>
public string GetTableScript(string dbName, string tName)
{
StringBuilder sb = new StringBuilder();
Table table = ser.Databases[dbName].Tables[tName];
Scripter script = GetScripter();
UrnCollection urns = new UrnCollection();
urns.Add(table.Urn);
StringCollection sqlScript = script.Script(urns);
for (int i = 0; i < sqlScript.Count; i++)
{
sb.Append(sqlScript[i]);
sb.Append("\r\nGO\r\n");
}
//sb.Append(sqlScript[3]);
return(sb.ToString());
}
/// <summary>
/// 生成表脚本
/// </summary>
/// <param name="tables">数据源表</param>
/// <param name="fileName">文件路径</param>
/// <returns>是否成功</returns>
public bool GenerateTableScripts(string fileName)
{
try
{
TableCollection tables = _Database.Tables;
DateTime start = DateTime.Now;
using (Stream stream = new FileStream(fileName, FileMode.Append, FileAccess.Write, FileShare.None))
{
//声明统一资源名称集合对象
UrnCollection collection = null;
//声明字符串集合对象：存储collection中的所有string对象（在这里其中有3个string对象）
StringCollection sqls = null;
collection = new UrnCollection();
for (int i = 0; i < tables.Count; i++)
{
collection.Add(tables[i].Urn);
}
sqls = _Scripter.Script(collection);
//遍历字符串集合对象sqls中的string对象，选择要输出的脚本语句：
if (sqls != null && sqls.Count > 0)
{
//写入文件
byte[] bytes = null;
string temp = "";
foreach (string s in sqls)
{
temp = s + "\r\n";
bytes = Encoding.Default.GetBytes(temp);
stream.Write(bytes, 0, bytes.Length);
}
}
stream.Close();
}
DateTime end = DateTime.Now;
TimeSpan span = end - start;
double seconds = span.TotalSeconds;
Console.WriteLine("创建表所用时间" + seconds);
return(true);
}
catch (Exception ex)
{
return(false);
}
}
private UrnCollection CreateUrns(ServerConnection serverConnection)
{
IEnumerable <ScriptingObject> selectedObjects = new List <ScriptingObject>(this.Parameters.ScriptingObjects);
serverName = serverConnection.TrueName;
databaseName = new SqlConnectionStringBuilder(this.Parameters.ConnectionString).InitialCatalog;
UrnCollection urnCollection = new UrnCollection();
foreach (var scriptingObject in selectedObjects)
{
if (string.IsNullOrEmpty(scriptingObject.Schema))
{
// TODO: get the default schema
scriptingObject.Schema = "dbo";
}
urnCollection.Add(scriptingObject.ToUrn(serverName, databaseName));
}
return(urnCollection);
}
private string GenerateScriptAs(Server server, UrnCollection urns, ScriptingOptions options)
{
SqlServer.Management.Smo.Scripter scripter = null;
string resultScript = string.Empty;
try
{
scripter = new SqlServer.Management.Smo.Scripter(server);
if (this.Parameters.Operation == ScriptingOperationType.Alter)
{
options.ScriptForAlter = true;
foreach (var urn in urns)
{
SqlSmoObject smoObject = server.GetSmoObject(urn);
// without calling the toch method, no alter script get generated from smo
smoObject.Touch();
}
}
scripter.Options = options;
scripter.ScriptingError += ScripterScriptingError;
var result = scripter.Script(urns);
resultScript = GetScript(options, result);
}
catch
{
throw;
}
finally
{
if (scripter != null)
{
scripter.ScriptingError -= this.ScripterScriptingError;
}
}
return(resultScript);
}
public static void Demonstration()
{
using (var conn = new SqlConnection(connectionString))
{
// 初始化一个连接
Server server = new Server(new ServerConnection(conn));
//得到数据库
var srcDb = server.Databases[conn.Database];
//得到表
Table table = srcDb.Tables["Alarms"];
//初始化Scripter
Scripter scripter = new Scripter(server);
scripter.Options.Add(ScriptOption.DriAllConstraints);
scripter.Options.Add(ScriptOption.DriAllKeys);
scripter.Options.Add(ScriptOption.Default);
scripter.Options.Add(ScriptOption.ContinueScriptingOnError);
scripter.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);
scripter.Options.Add(ScriptOption.Indexes);
UrnCollection collection = new UrnCollection();
collection.Add(table.Urn);
var createTableSql = new StringBuilder();
var sqls = scripter.Script(collection);
foreach (var s in sqls)
{
createTableSql.AppendLine(s.Replace("Alarms", "Alarms" + DateTime.Now.ToString("yyyyMMddHHmmss")));
}
srcDb.ExecuteNonQuery(createTableSql.ToString());
Console.WriteLine("创建完毕");
}
}
static void add_urns_from_collection(SchemaCollectionBase coll, UrnCollection urns, bool progress = true)
{
var dt = DateTime.UtcNow;
Console.WriteLine("PROCESSING {0}", coll.GetType().Name);
int count = coll.Count;
int idx = 0;
foreach (SqlSmoObject ob in coll)
{
if (progress)
{
util.drawTextProgressBar(++idx, count, NormalizeUrn(ob.Urn));
}
if (ob is StoredProcedure)
{
if (!((StoredProcedure)ob).IsSystemObject)
{
urns.Add(ob.Urn);
}
}
else
{
PropertyInfo isSystem = ob.GetType().GetProperty("IsSystemObject");
if (null == isSystem ||
(!(bool)isSystem.GetValue(ob))
)
{
urns.Add(ob.Urn);
}
}
}
Console.WriteLine("PROCESSED {0} {1}ms", coll.GetType().Name, DateTime.UtcNow.Subtract(dt).TotalMilliseconds);
}
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);
}
}
static void add_urn_from_query(Database db, string obj
, Func <string, string, string> geturn
, UrnCollection urns
, bool progress = true
, Func <string, string, bool> validate = null
)
{
var dt = DateTime.UtcNow;
Console.WriteLine("PROCESSING {0}", obj);
/*
* select CAST(
* case
* when sp.is_ms_shipped = 1 then 1
* when (
* select
* major_id
* from
* sys.extended_properties
* where
* major_id = sp.object_id and
* minor_id = 0 and
* class = 1 and
* name = N'microsoft_database_tools_support')
* is not null then 1
* else 0
* end
* AS bit) AS [IsSystemObject],
* from sys.objects sp
* where type = 'P'
*/
DataSet ds = db.ExecuteWithResults(@"select s.name as [schema], o.name from sys.objects o
inner join sys.schemas s
on o.schema_id = s.schema_id where type = '" + obj + "'");
int count = ds.Tables[0].Rows.Count;
int idx = 0;
foreach (DataRow r in ds.Tables[0].Rows)
{
if (progress)
{
util.drawTextProgressBar(++idx, count);
}
bool add = true;
if (null != validate)
{
add = validate(r[1].ToString(), r[0].ToString());
}
//urns.Add(db.StoredProcedures[r[1].ToString(), r[0].ToString()].Urn);
if (add)
{
urns.Add(geturn(r[1].ToString(), r[0].ToString()));
}
}
Console.WriteLine("PROCESSED {0} {1}ms", obj, DateTime.UtcNow.Subtract(dt).TotalMilliseconds);
}
public override string OpenTable(string database, string table)
{
string message = string.Empty;
using (SqlConnection connection = new SqlConnection(this.DbConnectionStringBuilder.ConnectionString))
{
Server server = new Server(new ServerConnection(connection));
server.ConnectionContext.Connect();
Table temp = server.Databases[database].Tables[table];
//初始化Scripter
Scripter a = new Scripter(server);
a.Options.Add(ScriptOption.DriAllConstraints);
a.Options.Add(ScriptOption.DriAllKeys);
a.Options.Add(ScriptOption.Default);
a.Options.Add(ScriptOption.ContinueScriptingOnError);
a.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);
a.Options.Add(ScriptOption.IncludeIfNotExists);
UrnCollection collection = new UrnCollection();
collection.Add(temp.Urn);
var sqls = a.Script(collection);
foreach (var s in sqls)
{
message += s;
}
}
return message;
}
public static string ScriptReferencingForeignKeys(ServerConnection conn, long refTableId)
{
string cmdText = @"select distinct fk.constid , const.name 'constname'
, sopk.id 'reftableid',sopk.name 'reftablename'
, sofk.id 'fktableid',sofk.name 'fktablename'
from sysforeignkeys fk
join sysobjects sopk on fk.rkeyid = sopk.id
join sysobjects sofk on fk.fkeyid = sofk.id
join sysobjects const on fk.constid = const.id";
cmdText += "\r\n";
cmdText += "where sopk.id = " + refTableId.ToString();
StringBuilder result = new StringBuilder();
Server srv = new Server(conn);
Database db = srv.Databases[conn.DatabaseName];
Table tbl = null;
DataTable records = new DataTable();
using (SqlConnection tmpConn = new SqlConnection(conn.ConnectionString))
{
tmpConn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmdText, tmpConn);
try
{
adapter.Fill(records);
if (records.Rows.Count == 0)
{
return(string.Empty);
}
UrnCollection urns = new UrnCollection();
string fkTableName = String.Empty;
string refTableName = String.Empty;
foreach (DataRow row in records.Rows)
{
fkTableName = (string)row["fktablename"];
refTableName = (string)row["reftablename"];
tbl = db.Tables[fkTableName];
foreach (ForeignKey fk in tbl.ForeignKeys)
{
if (fk.ReferencedTable.ToLowerInvariant() == refTableName.ToLowerInvariant())
{
result.AppendLine(CompactStrings(fk.Script()));
}
}
}
}
finally
{
adapter = null;
records = null;
}
}
return(result.ToString());
}
public override void Execute()
{
SqlServer.Management.Smo.Scripter scripter = null;
try
{
this.CancellationToken.ThrowIfCancellationRequested();
this.ValidateScriptDatabaseParams();
this.CancellationToken.ThrowIfCancellationRequested();
string resultScript = string.Empty;
// TODO: try to use one of the existing connections
using (SqlConnection sqlConnection = new SqlConnection(this.Parameters.ConnectionString))
{
sqlConnection.Open();
ServerConnection serverConnection = new ServerConnection(sqlConnection);
Server server = new Server(serverConnection);
scripter = new SqlServer.Management.Smo.Scripter(server);
ScriptingOptions options = new ScriptingOptions();
SetScriptBehavior(options);
PopulateAdvancedScriptOptions(this.Parameters.ScriptOptions, options);
options.WithDependencies = false;
options.ScriptData = false;
SetScriptingOptions(options);
// TODO: Not including the header by default. We have to get this option from client
options.IncludeHeaders = false;
scripter.Options = options;
scripter.Options.ScriptData = false;
scripter.ScriptingError += ScripterScriptingError;
UrnCollection urns = CreateUrns(serverConnection);
var result = scripter.Script(urns);
resultScript = GetScript(options, result);
}
this.CancellationToken.ThrowIfCancellationRequested();
Logger.Write(
LogLevel.Verbose,
string.Format(
"Sending script complete notification event for operation {0}",
this.OperationId
));
ScriptText = resultScript;
this.SendCompletionNotificationEvent(new ScriptingCompleteParams
{
Success = true,
});
}
catch (Exception e)
{
if (e.IsOperationCanceledException())
{
Logger.Write(LogLevel.Normal, string.Format("Scripting operation {0} was canceled", this.OperationId));
this.SendCompletionNotificationEvent(new ScriptingCompleteParams
{
Canceled = true,
});
}
else
{
Logger.Write(LogLevel.Error, string.Format("Scripting operation {0} failed with exception {1}", this.OperationId, e));
this.SendCompletionNotificationEvent(new ScriptingCompleteParams
{
OperationId = OperationId,
HasError = true,
ErrorMessage = e.Message,
ErrorDetails = e.ToString(),
});
}
}
finally
{
if (scripter != null)
{
scripter.ScriptingError -= this.ScripterScriptingError;
}
}
}
/// <summary>
/// Generate a script for a database object.
/// </summary>
/// <param name="urns">The object to script</param>
/// <returns>String containg the script</returns>
private string GenerateScript (Urn urn)
{
UrnCollection urns = new UrnCollection ();
urns.Add (urn);
return GenerateScript (urns);
}
private void ScriptViews()
{
try
{
#region Options
ScriptingOptions so = new ScriptingOptions();
so.IncludeHeaders = false;
// so.NoExecuteAs = true;
so.AllowSystemObjects = true;
so.NoCommandTerminator = false;
#endregion
Server srv = null;
Database db = GetDatabase(ref srv);
srv.SetDefaultInitFields(typeof(View), "IsSystemObject");
UrnCollection urns = new UrnCollection();
foreach (View v in db.Views)
{
// exclude these objects
if (v.IsSystemObject) continue;
if (v.Name.StartsWith("aspnet_")) continue;
urns.Add(v.Urn);
}
string filepath = this.BuildScriptFilePath(ViewDirectory, null);
CreateDirectory(filepath);
StringCollection FileCollection = new StringCollection();
if (urns.Count > 0)
{
DependencyWalker depwalker = new Microsoft.SqlServer.Management.Smo.DependencyWalker(srv);
DependencyTree tree = depwalker.DiscoverDependencies(urns, true);
DependencyCollection depcoll = depwalker.WalkDependencies(tree);
foreach (DependencyCollectionNode dep in depcoll)
{
if (dep.Urn.Type == "View")
{
foreach (View t in db.Views)
{
if (t.Name.ToString() == dep.Urn.GetAttribute("Name").ToString())
{
if (!t.IsEncrypted)
{
string viewFilePath = Path.Combine(filepath, t.Schema + "." + t.Name.ToString() + ".sql");
so.ScriptDrops = true;
StringCollection drop = t.Script(so);
so.ScriptDrops = false;
StringCollection create = t.Script();
StringCollection sc = new StringCollection();
sc.Add("IF EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='V' AND NAME='" + t.Name.ToString() + "')\r\nBEGIN\r\n");
foreach (string s in drop)
{
sc.Add(s);
}
sc.Add("\r\nEND\r\nGO\r\n");
foreach (string s in create)
{
sc.Add(s);
sc.Add("GO\r\n");
}
WriteFile(viewFilePath, sc);
string relativeViewFilePath = this.BuildRelativeFilePath(viewFilePath);
FileCollection.Add(relativeViewFilePath); // We want a relative path here.
WriteStatus("File written: " + relativeViewFilePath);
break;
}
else
{
WriteStatus("WARNING: View is NOT scripted. It was found to be encrypted and cannot be scripted. (" + t.Name + ")");
}
}
}
}
}
} //if urns.Count > 0
string viewListFilePath = this.BuildScriptFilePath(ViewDirectory, ViewsFile);
WriteFile(viewListFilePath, FileCollection);
string relativeViewListFilePath = this.BuildRelativeFilePath(viewListFilePath);
WriteStatus("File written: " + relativeViewListFilePath);
}
catch (Exception err)
{
WriteStatus(Thread.CurrentThread.ThreadState.ToString());
if (Thread.CurrentThread.ThreadState != ThreadState.AbortRequested)
{
WriteStatus("An error may have been encountered. Please check the log file for more details.");
MMDB.Core.MMDBLogFile.Log(err, "ScriptView");
throw new ViewScriptingException("A View scripting exception has been found. Please review the log for more information.", err);
}
}
}
private void ScriptPartitionFunction()
{
try
{
#region Options
ScriptingOptions so = new ScriptingOptions();
so.IncludeHeaders = false;
// so.NoExecuteAs = true;
so.AllowSystemObjects = true;
so.NoCommandTerminator = false;
#endregion
Server srv = null;
Database db = GetDatabase(ref srv);
UrnCollection urns = new UrnCollection();
foreach (PartitionFunction v in db.PartitionFunctions)
{
// exclude these objects
urns.Add(v.Urn);
}
string filepath = this.BuildScriptFilePath(DatabaseDirectory, null);
CreateDirectory(filepath);
StringCollection FileCollection = new StringCollection();
if (urns.Count > 0)
{
DependencyWalker depwalker = new Microsoft.SqlServer.Management.Smo.DependencyWalker(srv);
DependencyTree tree = depwalker.DiscoverDependencies(urns, true);
DependencyCollection depcoll = depwalker.WalkDependencies(tree);
foreach (DependencyCollectionNode dep in depcoll)
{
if (dep.Urn.Type == "PartitionFunction")
{
foreach (PartitionFunction t in db.PartitionFunctions)
{
if (t.Name.ToString() == dep.Urn.GetAttribute("Name").ToString())
{
string PartitionFunctionFilePath = Path.Combine(filepath, t.Name.ToString() + ".sql");
so.ScriptDrops = true;
StringCollection drop = t.Script(so);
so.ScriptDrops = false;
StringCollection create = t.Script();
StringCollection sc = new StringCollection();
foreach (string s in create)
{
sc.Add(s);
sc.Add("GO\r\n");
}
WriteFile(PartitionFunctionFilePath, sc);
string relativePartitionFunctionFilePath = this.BuildRelativeFilePath(PartitionFunctionFilePath);
FileCollection.Add(relativePartitionFunctionFilePath); // We want a relative path here.
WriteStatus("File written: " + relativePartitionFunctionFilePath);
break;
}
}
}
}
} //if urns.Count > 0
string PartitionFunctionListFilePath = this.BuildScriptFilePath(DatabaseDirectory, PartitionFunctionFile);
WriteFile(PartitionFunctionListFilePath, FileCollection);
string relativePartitionFunctionListFilePath = this.BuildRelativeFilePath(PartitionFunctionListFilePath);
WriteStatus("File written: " + relativePartitionFunctionListFilePath);
}
catch (Exception err)
{
WriteStatus(Thread.CurrentThread.ThreadState.ToString());
if (Thread.CurrentThread.ThreadState != ThreadState.AbortRequested)
{
WriteStatus("An error may have been encountered. Please check the log file for more details.");
MMDB.Core.MMDBLogFile.Log(err, "ScriptPartitionFunction");
throw new PartitionFunctionScriptingException("A Partition Scheme scripting exception has been found. Please read the log for more information.", err);
}
}
}
private string GenerareScriptAsExecute(Server server, UrnCollection urns, ScriptingOptions options)
{
string script = string.Empty;
ScriptingObject scriptingObject = this.Parameters.ScriptingObjects[0];
Urn urn = urns[0];
// get the object
StoredProcedure sp = server.GetSmoObject(urn) as StoredProcedure;
Database parentObject = server.GetSmoObject(urn.Parent) as Database;
StringBuilder executeStatement = new StringBuilder();
// list of DECLARE <variable> <type>
StringBuilder declares = new StringBuilder();
// Parameters to be passed
StringBuilder parameterList = new StringBuilder();
if (sp == null || parentObject == null)
{
throw new InvalidOperationException(SR.ScriptingExecuteNotSupportedError);
}
WriteUseDatabase(parentObject, executeStatement, options);
// character string to put in front of each parameter. First one is just carriage return
// the rest will have a "," in front as well.
string newLine = Environment.NewLine;
string paramListPreChar = $"{newLine} ";
for (int i = 0; i < sp.Parameters.Count; i++)
{
StoredProcedureParameter spp = sp.Parameters[i];
declares.AppendFormat("DECLARE {0} {1}{2}"
, QuoteObjectName(spp.Name)
, GetDatatype(spp.DataType, options)
, newLine);
parameterList.AppendFormat("{0}{1}"
, paramListPreChar
, QuoteObjectName(spp.Name));
// if this is the first time through change the prefix to include a ","
if (i == 0)
{
paramListPreChar = $"{newLine} ,";
}
// mark any output parameters as such.
if (spp.IsOutputParameter)
{
parameterList.Append(" OUTPUT");
}
}
// build the execute statement
if (sp.ImplementationType == ImplementationType.TransactSql)
{
executeStatement.Append("EXECUTE @RC = ");
}
else
{
executeStatement.Append("EXECUTE ");
}
// get the object name
executeStatement.Append(GenerateSchemaQualifiedName(sp.Schema, sp.Name, options.SchemaQualify));
string formatString = sp.ImplementationType == ImplementationType.TransactSql
? "DECLARE @RC int{5}{0}{5}{1}{5}{5}{2} {3}{5}{4}"
: "{0}{5}{1}{5}{5}{2} {3}{5}{4}";
script = string.Format(CultureInfo.InvariantCulture, formatString,
declares,
SR.StoredProcedureScriptParameterComment,
executeStatement,
parameterList,
CommonConstants.DefaultBatchSeperator,
newLine);
return(script);
}
static void Main(string[] args)
{
try
{
Server server = new Server();
Database db = server.Databases["SnCore"];
if (db == null) throw new Exception("Missing SnCore database");
Scripter scripter = new Scripter(server);
scripter.Options.Default = true;
scripter.Options.DriAll = true;
scripter.Options.AllowSystemObjects = true;
scripter.Options.AnsiFile = true;
scripter.Options.AppendToFile = false;
scripter.Options.FileName = "SnCoreSqlServer.sql";
scripter.Options.FullTextCatalogs = true;
scripter.Options.FullTextIndexes = true;
scripter.Options.IncludeDatabaseRoleMemberships = false;
scripter.Options.IncludeHeaders = false;
scripter.Options.Indexes = true;
scripter.Options.NoIdentities = false;
scripter.Options.NonClusteredIndexes = true;
scripter.Options.ClusteredIndexes = true;
scripter.Options.SchemaQualifyForeignKeysReferences = true;
scripter.Options.Permissions = false;
scripter.Options.ScriptDrops = false;
scripter.Options.Statistics = false;
scripter.Options.ToFileOnly = true;
scripter.Options.Triggers = true;
scripter.Options.IncludeIfNotExists = true;
UrnCollection c = new UrnCollection();
UrnCollection fk = new UrnCollection();
List<Table> tables = new List<Table>();
IEnumerator e = db.Tables.GetEnumerator();
while (e.MoveNext()) tables.Add((Table) e.Current);
tables.Sort(CompareTablesByName);
Console.WriteLine("Processing {0} tables ...", tables.Count);
foreach (Table tb in tables)
{
c.Add(tb.Urn);
foreach (Check check in tb.Checks)
c.Add(check.Urn);
foreach (Index index in tb.Indexes)
c.Add(index.Urn);
// foreign keys are added last since tables are sorted in alphabetical order
// target table may not exist during creation if foreign keys are set before all tables are created
foreach (ForeignKey key in tb.ForeignKeys)
fk.Add(key.Urn);
if (tb.FullTextIndex != null)
c.Add(tb.FullTextIndex.Urn);
}
c.AddRange(fk);
scripter.ScriptingProgress += new ProgressReportEventHandler(scripter_ScriptingProgress);
scripter.ScriptingError += new ScriptingErrorEventHandler(scripter_ScriptingError);
scripter.Script(c);
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
}
/// <summary>
/// 获取视图的脚本
/// </summary>
/// <param name="dbName"></param>
/// <param name="vName"></param>
/// <returns></returns>
public string GetViewScript(string dbName, string vName)
{
StringBuilder sb = new StringBuilder();
View view = ser.Databases[dbName].Views[vName];
Scripter script = GetScripter();
UrnCollection urns = new UrnCollection();
urns.Add(view.Urn);
StringCollection sqlScript = script.Script(urns);
for (int i = 0; i < sqlScript.Count; i++)
{
sb.Append(sqlScript[i]);
sb.Append("\r\nGO\r\n");
}
return sb.ToString();
}
static void add_urn_from_query(Database db, string obj, Func <string, string, string> geturn, UrnCollection urns, bool progress = true)
{
var dt = DateTime.UtcNow;
Console.WriteLine("PROCESSING {0}", obj);
DataSet ds = db.ExecuteWithResults(@"select s.name as [schema], o.name from sys.objects o
inner join sys.schemas s
on o.schema_id = s.schema_id where type = '" + obj + "'");
int count = ds.Tables[0].Rows.Count;
int idx = 0;
foreach (DataRow r in ds.Tables[0].Rows)
{
if (progress)
{
drawTextProgressBar(++idx, count);
}
//urns.Add(db.StoredProcedures[r[1].ToString(), r[0].ToString()].Urn);
urns.Add(geturn(r[1].ToString(), r[0].ToString()));
}
Console.WriteLine("PROCESSED {0} {1}ms", obj, DateTime.UtcNow.Subtract(dt).TotalMilliseconds);
}
/// <summary>
/// Scripts the schema.
/// </summary>
/// <param name="connectionString">The connection string.</param>
/// <returns>A Hashtable containing a list of table names and their corresponding StringBuilder</returns>
public static Dictionary<string, StringBuilder> ScriptSchema(string connectionString, DataProvider provider, bool oneFile)
{
StringBuilder result = new StringBuilder();
SqlConnection conn = new SqlConnection(connectionString);
SqlConnectionStringBuilder cString = new SqlConnectionStringBuilder(connectionString);
ServerConnection sconn = new ServerConnection(conn);
Server server = new Server(sconn);
Database db = server.Databases[cString.InitialCatalog];
Dictionary<string, StringBuilder> dict = new Dictionary<string, StringBuilder>();
if (oneFile)
{
Transfer trans = new Transfer(db);
//set the objects to copy
trans.CopyAllTables = false;
trans.CopyAllDefaults = false;
trans.CopyAllUserDefinedFunctions = true;//we don't have logic in SubSonic to decide which ones should or should not be generated, so better to be safe.
trans.CopyAllStoredProcedures = false;
trans.CopyAllViews = false;
trans.CopySchema = false;
trans.CopyAllLogins = false;
foreach (Table tbl in db.Tables)
{
if (!CodeService.ShouldGenerate(tbl.Name, provider.Name))
continue;
Utilities.Utility.WriteTrace(string.Format("Adding table {0}", tbl.Name));
trans.ObjectList.Add(tbl);
}
foreach (View v in db.Views)
{
if (!CodeService.ShouldGenerate(v.Name, provider.Name))
continue;
Utilities.Utility.WriteTrace(string.Format("Adding view {0}", v.Name));
trans.ObjectList.Add(v);
}
foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure sp in db.StoredProcedures)
{
if (!provider.UseSPs || !CodeService.ShouldGenerate(sp.Name, provider.IncludeProcedures, provider.ExcludeProcedures, provider))
continue;
Utilities.Utility.WriteTrace(string.Format("Adding sproc {0}", sp.Name));
trans.ObjectList.Add(sp);
}
trans.CopyData = false;
trans.DropDestinationObjectsFirst = true;
trans.UseDestinationTransaction = true;
trans.Options.AnsiFile = true;
trans.Options.ScriptBatchTerminator = true;
trans.Options.WithDependencies = true; //if this setting is false and you get an error, try installing SQL Server 2008 SP1 cumulative update 5 or higher..see http://support.microsoft.com/kb/976413
trans.Options.DriAll = true;
trans.Options.IncludeHeaders = false;
trans.Options.IncludeIfNotExists = true;
trans.Options.SchemaQualify = true;
Utilities.Utility.WriteTrace("Scripting objects...");
StringCollection script = trans.ScriptTransfer();
foreach (string s in script)
result.AppendLine(s);
result.AppendLine();
result.AppendLine();
dict.Add(provider.Name, result);
return dict;
}
else
{
//use this method to append single tables and all of their dependencies one at a time
Scripter scr = new Scripter(server);
scr.Options.AnsiFile = true;
scr.Options.ClusteredIndexes = true;
scr.Options.DriAll = true;
scr.Options.IncludeHeaders = false;
scr.Options.IncludeIfNotExists = true;
scr.Options.SchemaQualify = true;
scr.Options.WithDependencies = false;
UrnCollection u = new UrnCollection();
foreach (Table tbl in db.Tables)
{
if (CodeService.ShouldGenerate(tbl.Name, provider.Name))
{
u = new UrnCollection();
u.Add(tbl.Urn);
if (!tbl.IsSystemObject)
{
Utilities.Utility.WriteTrace(string.Format("Adding table {0}", tbl.Name));
result = new StringBuilder();
StringCollection sc = scr.Script(u);
foreach (string s in sc)
result.AppendLine(s);
dict.Add(string.Concat("Table_",tbl.Name), result);
}
}
}
foreach (View v in db.Views)
{
if (CodeService.ShouldGenerate(v.Name, provider.Name))
{
u = new UrnCollection();
u.Add(v.Urn);
if (!v.IsSystemObject)
{
Utilities.Utility.WriteTrace(string.Format("Adding view {0}", v.Name));
result = new StringBuilder();
StringCollection sc = scr.Script(u);
foreach (string s in sc)
result.AppendLine(s);
dict.Add(string.Concat("View_",v.Name), result);
}
}
}
foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure sp in db.StoredProcedures)
{
if (CodeService.ShouldGenerate(sp.Name, provider.IncludeProcedures, provider.ExcludeProcedures, provider))
{
u = new UrnCollection();
u.Add(sp.Urn);
if (!sp.IsSystemObject)
{
Utilities.Utility.WriteTrace(string.Format("Adding sproc {0}", sp.Name));
result = new StringBuilder();
StringCollection sc = scr.Script(u);
foreach (string s in sc)
result.AppendLine(s);
dict.Add(string.Concat("Sproc_",sp.Name), result);
}
}
}
foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
{
if (CodeService.ShouldGenerate(udf.Name, provider.IncludeProcedures, provider.ExcludeProcedures, provider))
{
u = new UrnCollection();
u.Add(udf.Urn);
if (!udf.IsSystemObject)
{
Utilities.Utility.WriteTrace(string.Format("Adding udf {0}", udf.Name));
result = new StringBuilder();
StringCollection sc = scr.Script(u);
foreach (string s in sc)
result.AppendLine(s);
dict.Add(string.Concat("UDF_", udf.Name), result);
}
}
}
return dict;
}
}
static void Main(string[] args)
{
try
{
Server server = new Server();
Database db = server.Databases["DBlog"];
if (db == null)
{
db = server.Databases["Blog"];
}
if (db == null)
{
throw new Exception("Missing DBlog database");
}
Scripter scripter = new Scripter(server);
scripter.Options.Default = true;
scripter.Options.DriAll = true;
scripter.Options.AllowSystemObjects = true;
scripter.Options.AnsiFile = true;
scripter.Options.AppendToFile = false;
scripter.Options.FileName = "DBlog.sql";
scripter.Options.FullTextCatalogs = true;
scripter.Options.FullTextIndexes = true;
scripter.Options.IncludeDatabaseRoleMemberships = false;
scripter.Options.IncludeHeaders = false;
scripter.Options.Indexes = true;
scripter.Options.NoIdentities = false;
scripter.Options.NonClusteredIndexes = true;
scripter.Options.ClusteredIndexes = true;
scripter.Options.SchemaQualifyForeignKeysReferences = true;
scripter.Options.Permissions = false;
scripter.Options.ScriptDrops = false;
scripter.Options.Statistics = false;
scripter.Options.ToFileOnly = true;
scripter.Options.Triggers = true;
scripter.Options.IncludeIfNotExists = true;
UrnCollection c = new UrnCollection();
UrnCollection fk = new UrnCollection();
List <Table> tables = new List <Table>();
IEnumerator e = db.Tables.GetEnumerator();
while (e.MoveNext())
{
tables.Add((Table)e.Current);
}
tables.Sort(CompareTablesByName);
Console.WriteLine("Processing {0} tables ...", tables.Count);
foreach (Table tb in tables)
{
c.Add(tb.Urn);
foreach (Check check in tb.Checks)
{
c.Add(check.Urn);
}
foreach (Index index in tb.Indexes)
{
c.Add(index.Urn);
}
// foreign keys are added last since tables are sorted in alphabetical order
// target table may not exist during creation if foreign keys are set before all tables are created
foreach (ForeignKey key in tb.ForeignKeys)
{
fk.Add(key.Urn);
}
if (tb.FullTextIndex != null)
{
c.Add(tb.FullTextIndex.Urn);
}
}
c.AddRange(fk);
scripter.ScriptingProgress += new ProgressReportEventHandler(scripter_ScriptingProgress);
scripter.ScriptingError += new ScriptingErrorEventHandler(scripter_ScriptingError);
scripter.Script(c);
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
}
public static void Main(string[] args)
{
var dataSource = args.Length > 0 ? args[0] : "localhost"; // 172.17.0.5,1433 192.168.56.1,1435, mydb.database.windows.net,1433
var dbName = args.Length > 1 ? args[1] : "master";
var user = args.Length > 2 ? args[2] : "sa";
var outputFolderSql = args.Length > 3 ? args[3] : "";
Console.WriteLine("Password for user {0} for database {1} at {2}: ", user, dbName, dataSource);
var password = GetPassword();
SqlServerVersion targetServerVersion = SqlServerVersion.Version140;
DatabaseEngineEdition targetDatabaseEngineEdition = DatabaseEngineEdition.Standard;
DatabaseEngineType targetDatabaseEngineType = DatabaseEngineType.Standalone;
String outputFileBase = dbName + "@" + @dataSource + "_" + DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss");
String outputFileSql = outputFileBase + ".sql";
String outputFilePathSql = Path.Combine(outputFolderSql, outputFileSql);
Console.WriteLine("Connecting to database... ");
var connectionString = new SqlConnectionStringBuilder
{
PersistSecurityInfo = true, // Needed - see https://github.com/Microsoft/sqltoolsservice/issues/372#issuecomment-307190530
DataSource = @dataSource,
UserID = user,
Password = password,
InitialCatalog = dbName,
PacketSize = 4096, // default is 8000, however 4096 gives much better performance when accessing azure database
//IntegratedSecurity = true, // Log in using SQL authentication or Windows authentication
//MultipleActiveResultSets = false,
//Encrypt = false,
//TrustServerCertificate = false,
};
var sqlConnection = new SqlConnection(connectionString.ConnectionString);
var serverConnection = new ServerConnection(sqlConnection);
var sqlServer = new Server(serverConnection);
//sqlServer.ConnectionContext.BatchSeparator = "GO";
// Improve performance by just loading fields we need
sqlServer.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(Table), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(View), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(Trigger), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(SqlAssembly), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(Default), false);
sqlServer.SetDefaultInitFields(typeof(Rule), false);
sqlServer.SetDefaultInitFields(typeof(UserDefinedAggregate), false);
sqlServer.SetDefaultInitFields(typeof(Synonym), false);
sqlServer.SetDefaultInitFields(typeof(Sequence), false);
sqlServer.SetDefaultInitFields(typeof(SecurityPolicy), false);
sqlServer.SetDefaultInitFields(typeof(UserDefinedDataType), false);
sqlServer.SetDefaultInitFields(typeof(XmlSchemaCollection), false);
sqlServer.SetDefaultInitFields(typeof(UserDefinedType), false);
sqlServer.SetDefaultInitFields(typeof(UserDefinedTableType), false);
sqlServer.SetDefaultInitFields(typeof(PartitionScheme), false);
sqlServer.SetDefaultInitFields(typeof(PartitionFunction), false);
sqlServer.SetDefaultInitFields(typeof(PlanGuide), false);
sqlServer.SetDefaultInitFields(typeof(FullTextCatalog), false);
var db = sqlServer.Databases[dbName];
Console.WriteLine("Connected!");
Console.WriteLine("Generating script for database " + dbName + ". This may take a while...");
// ### First script "CREATE DATABASE ..."
var createDbScriptOptions = DefaultOptions(targetServerVersion, targetDatabaseEngineEdition, targetDatabaseEngineType);
createDbScriptOptions.FileName = outputFilePathSql;
createDbScriptOptions.ScriptData = false;
createDbScriptOptions.WithDependencies = false;
db.Script(createDbScriptOptions);
Console.WriteLine("Collecting objects...");
// ### Now script all the objects within the database
var urns = new UrnCollection();
Console.WriteLine("...Tables");
foreach (Table obj in db.Tables)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...Views");
foreach (View obj in db.Views)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...UserDefinedFunctions");
foreach (UserDefinedFunction obj in db.UserDefinedFunctions)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...Defaults");
foreach (Default obj in db.Defaults)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Rules");
foreach (Rule obj in db.Rules)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Triggers");
foreach (Trigger obj in db.Triggers)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...UserDefinedAggregates");
foreach (UserDefinedAggregate obj in db.UserDefinedAggregates)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Synonyms");
foreach (Synonym obj in db.Synonyms)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Sequences");
foreach (Sequence obj in db.Sequences)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...SecurityPolicies");
foreach (SecurityPolicy obj in db.SecurityPolicies)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...UserDefinedDataTypes");
foreach (UserDefinedDataType obj in db.UserDefinedDataTypes)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...XmlSchemaCollections");
foreach (XmlSchemaCollection obj in db.XmlSchemaCollections)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...UserDefinedTypes");
foreach (UserDefinedType obj in db.UserDefinedTypes)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...UserDefinedTableTypes");
foreach (UserDefinedTableType obj in db.UserDefinedTableTypes)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...PartitionSchemes");
foreach (PartitionScheme obj in db.PartitionSchemes)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...PartitionFunctions");
foreach (PartitionFunction obj in db.PartitionFunctions)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...PlanGuides");
foreach (PlanGuide obj in db.PlanGuides)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Assemblies");
foreach (SqlAssembly obj in db.Assemblies)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...StoredProcedures");
foreach (StoredProcedure obj in db.StoredProcedures)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
var ftUrns = new UrnCollection();
Console.WriteLine("...FullTextCatalogs");
foreach (FullTextCatalog obj in db.FullTextCatalogs)
{
Console.WriteLine(" -> " + obj.Name);
ftUrns.Add(obj.Urn);
}
var scripter = new Scripter(sqlServer)
{
PrefetchObjects = true, // some sources suggest this may speed things up
};
scripter.Options = DefaultOptions(targetServerVersion, targetDatabaseEngineEdition, targetDatabaseEngineType);
scripter.Options.FileName = outputFilePathSql;
scripter.ScriptingProgress += (o, e) => Console.WriteLine("Scripting: {0}", e.Current);
scripter.ScriptingError += (o, e) => Console.WriteLine("Error: {0}", e.Current);
scripter.Options.WithDependencies = false;
scripter.EnumScript(ftUrns);
scripter.Options.WithDependencies = true;
scripter.EnumScript(urns);
Console.WriteLine("Scripting finished! See File:");
Console.WriteLine(outputFilePathSql);
// And now we also could create a backup (but be aware the file is saved ON THE SERVER
// (Maybe we could save it locally somehow via DeviceType.Pipe, DeviceType.Url, DeviceType.VirtualDevice, etc...)
//String outputFileBak = outputFileBase + ".bak";
//String outputFilePathBak = outputFileBak;
//Console.WriteLine();
//Console.WriteLine("And now we create a backup...");
//var backup = new Backup
//{
// Action = BackupActionType.Database,
// Database = dbName
//};
//backup.Devices.Add(new BackupDeviceItem(outputFilePathBak, DeviceType.File));
//backup.Initialize = true;
//backup.Checksum = true;
//backup.ContinueAfterError = false;
//backup.Incremental = false;
//backup.PercentCompleteNotification = 1;
//backup.LogTruncation = BackupTruncateLogType.Truncate;
//backup.PercentComplete += (s, e) => Console.WriteLine("Backup: {0}", e.Message);
//backup.Complete += (s, e) => Console.WriteLine("{0} See File: {1}", e.Error.Message, outputFilePathBak);
//backup.SqlBackupAsync(sqlServer);
// ----
// An alternative would be using the internal class ScriptPublishWizard via reflection (because we can't access internal stuff)
// ###################################################################
//new Microsoft.SqlServer.Management.SqlScriptPublish.ScriptPublishWizard();
//var assembly = Assembly.LoadFrom(@"C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.SqlServer.Management.SqlScriptPublishUI.dll");
// ----
// Bonus: Display all the configuration options.
// ###################################################################
//foreach (ConfigProperty p in sqlServer.Configuration.Properties)
//{
// Console.WriteLine(p.DisplayName + " " + p.ConfigValue + " " + p.RunValue);
//}
//Console.WriteLine("There are " + sqlServer.Configuration.Properties.Count.ToString() + " configuration options.");
////Display the maximum and minimum values for ShowAdvancedOptions.
//int min = sqlServer.Configuration.ShowAdvancedOptions.Minimum;
//int max = sqlServer.Configuration.ShowAdvancedOptions.Maximum;
//Console.WriteLine("Minimum and Maximum values are " + min + " and " + max + ".");
sqlConnection.Close();
}
private void ScriptTriggers()
{
try
{
#region Options
ScriptingOptions so = new ScriptingOptions();
so.NoExecuteAs = true;
so.ScriptDrops = false;
so.SchemaQualify = true;
so.AllowSystemObjects = false;
so.Triggers = true;
#endregion
Server srv = new Server();
Database db = GetDatabase(ref srv);
srv.SetDefaultInitFields(typeof(View), "IsSystemObject");
UrnCollection urns = new UrnCollection();
foreach (Table v in db.Tables)
{
foreach (Trigger t in v.Triggers)
{
// exclude these objects
if (t.IsSystemObject) continue;
if (t.Name.StartsWith("aspnet_")) continue;
urns.Add(t.Urn);
}
}
string filepath = this.BuildScriptFilePath(TriggerDirectory, null);
StringCollection FileCollection = new StringCollection();
CreateDirectory(filepath);
foreach (Table v in db.Tables)
{
foreach (Trigger t in v.Triggers)
{
if (!t.IsSystemObject)
{
string triggerFilePath = Path.Combine(filepath, v.Schema + "." + t.Name.ToString() + ".sql");
so.ScriptDrops = true;
StringCollection drop = t.Script(so);
so.ScriptDrops = false;
t.TextMode = false;
StringCollection create = t.Script();
StringCollection sc = new StringCollection();
if (t.IsEnabled)
{
sc.Add("IF EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='TR' AND NAME='" + t.Name.ToString() + "')\r\nBEGIN\r\n");
foreach (string s in drop)
{
sc.Add(s);
}
sc.Add("\r\nEND\r\nGO\r\n");
foreach (string s in create)
{
sc.Add(s);
sc.Add("GO\r\n");
}
WriteFile(triggerFilePath, sc);
string relativeTriggerFilePath = this.BuildRelativeFilePath(triggerFilePath);
FileCollection.Add(relativeTriggerFilePath); // We want a relative path here.
WriteStatus("File written: " + relativeTriggerFilePath);
}
}
else
{
WriteStatus("WARNING: is NOT scripted. It was found to be encrypted and cannot be scripted. (" + t.Name + ")");
}
}
}
string triggerListFilePath = this.BuildScriptFilePath(TriggerDirectory, TriggersFile);
WriteFile(triggerListFilePath, FileCollection);
string relativeTriggerListFilePath = this.BuildRelativeFilePath(triggerListFilePath);
WriteStatus("File written: " + relativeTriggerListFilePath);
}
catch (Exception err)
{
string name = "ScriptTriggers";
WriteStatus(name + "-" + Thread.CurrentThread.ThreadState.ToString());
if (Thread.CurrentThread.ThreadState != ThreadState.AbortRequested)
{
WriteStatus("An error may have been encountered. Please check the log file for more details.");
MMDB.Core.MMDBLogFile.Log(err, name);
throw new TriggerScriptingException("A trigger scripting exception has been found. Please review the log for more information.", err);
}
}
}
/// <summary>
/// 获取表的脚本
/// </summary>
/// <param name="dbName"></param>
/// <param name="tName"></param>
/// <returns></returns>
public string GetTableScript(string dbName, string tName)
{
StringBuilder sb = new StringBuilder();
Table table = ser.Databases[dbName].Tables[tName];
Scripter script = GetScripter();
UrnCollection urns = new UrnCollection();
urns.Add(table.Urn);
StringCollection sqlScript = script.Script(urns);
for (int i = 0; i < sqlScript.Count; i++)
{
sb.Append(sqlScript[i]);
sb.Append("\r\nGO\r\n");
}
//sb.Append(sqlScript[3]);
return sb.ToString();
}
public string getProcedureScript(string dbName, string pName)
{
StringBuilder sb = new StringBuilder();
StoredProcedure procedure = ser.Databases[dbName].StoredProcedures[pName];
Scripter script = GetScripter();
UrnCollection urns = new UrnCollection();
urns.Add(procedure.Urn);
StringCollection sqlScript = script.Script(urns);
foreach (string str in sqlScript)
{
sb.Append(str);
sb.Append("\r\nGO\r\n");
}
return sb.ToString();
}
public override void Execute()
{
try
{
this.CancellationToken.ThrowIfCancellationRequested();
this.ValidateScriptDatabaseParams();
this.CancellationToken.ThrowIfCancellationRequested();
string resultScript = string.Empty;
// TODO: try to use one of the existing connections
Server server = new Server(ServerConnection);
if (!ServerConnection.IsOpen)
{
ServerConnection.Connect();
}
UrnCollection urns = CreateUrns(ServerConnection);
ScriptingOptions options = new ScriptingOptions();
SetScriptBehavior(options);
ScriptAsOptions scriptAsOptions = new ScriptAsOptions(this.Parameters.ScriptOptions);
PopulateAdvancedScriptOptions(scriptAsOptions, options);
options.WithDependencies = false;
// TODO: Not including the header by default. We have to get this option from client
options.IncludeHeaders = false;
// Scripting data is not avaialable in the scripter
options.ScriptData = false;
SetScriptingOptions(options);
switch (this.Parameters.Operation)
{
case ScriptingOperationType.Create:
case ScriptingOperationType.Alter:
case ScriptingOperationType.Delete: // Using Delete here is wrong. delete usually means delete rows from table but sqlopsstudio sending the operation name as delete instead of drop
resultScript = GenerateScriptAs(server, urns, options);
break;
case ScriptingOperationType.Select:
resultScript = GenerateScriptSelect(server, urns);
break;
case ScriptingOperationType.Execute:
resultScript = GenerareScriptAsExecute(server, urns, options);
break;
}
this.CancellationToken.ThrowIfCancellationRequested();
Logger.Write(
TraceEventType.Verbose,
string.Format(
"Sending script complete notification event for operation {0}",
this.OperationId
));
ScriptText = resultScript;
this.SendCompletionNotificationEvent(new ScriptingCompleteParams
{
Success = true,
});
this.SendPlanNotificationEvent(new ScriptingPlanNotificationParams
{
ScriptingObjects = this.Parameters.ScriptingObjects,
Count = 1,
});
}
catch (Exception e)
{
if (e.IsOperationCanceledException())
{
Logger.Write(TraceEventType.Information, string.Format("Scripting operation {0} was canceled", this.OperationId));
this.SendCompletionNotificationEvent(new ScriptingCompleteParams
{
Canceled = true,
});
}
else
{
Logger.Write(TraceEventType.Error, string.Format("Scripting operation {0} failed with exception {1}", this.OperationId, e));
this.SendCompletionNotificationEvent(new ScriptingCompleteParams
{
OperationId = OperationId,
HasError = true,
ErrorMessage = $"{SR.ScriptingGeneralError} {e.Message}",
ErrorDetails = e.ToString(),
});
}
}
finally
{
if (disconnectAtDispose && ServerConnection != null && ServerConnection.IsOpen)
{
ServerConnection.Disconnect();
}
}
}
public string getFunctionScript(string dbName, string fName)
{
StringBuilder sb = new StringBuilder();
UserDefinedFunction fun = ser.Databases[dbName].UserDefinedFunctions[fName];
Scripter script = GetScripter();
UrnCollection urns = new UrnCollection();
urns.Add(fun.Urn);
StringCollection sqlScript = script.Script(urns);
foreach (string str in sqlScript)
{
sb.Append(str);
sb.Append("\r\nGO\n\r");
}
return sb.ToString();
}
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);
}
}
/// <summary>
/// Generate a script for a database object.
/// </summary>
/// <param name="urns">The objects to script</param>
/// <returns>String containg the script</returns>
private string GenerateScript (UrnCollection urns)
{
_scripter.Options.ContinueScriptingOnError = true;
// script the the list of objects passed in
StringCollection scriptCollection = _scripter.Script (urns);
StringBuilder sb = new StringBuilder ();
foreach (string s in scriptCollection)
sb.AppendLine (s);
return sb.ToString();
}