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