/// <summary> /// 数据库操作类 /// </summary> /// <param name="server"></param> /// <param name="userId"></param> /// <param name="password"></param> /// <param name="database"></param> public GenerateScripts(string server, string userId, string password, string database) { _Connection = new Microsoft.SqlServer.Management.Common.ServerConnection(server, userId, password); _Server = new Microsoft.SqlServer.Management.Smo.Server(_Connection); _Scripter = GetScripter(_Server); _Database = _Server.Databases[database]; }
public static void ScriptViews( Database database, string scriptDirectory, Microsoft.SqlServer.Management.Smo.Scripter scripter) { ScriptViews(database, scriptDirectory, scripter, false, ""); }
public static void ScriptTables( Database database, string scriptDirectory, Microsoft.SqlServer.Management.Smo.Scripter scripter, bool matchOnNameContains, string textToMatchOnNameContains) { string tableDirectory = Path.Combine(scriptDirectory, "tables"); System.IO.Directory.CreateDirectory(tableDirectory); Stopwatch blockStart = new Stopwatch(); blockStart.Start(); TableCollection allTables = database.Tables; List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> allTableObjects = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>(); int tableIndex = 0; ScriptMove.WriteToLog("Scripting tables..."); foreach (Table oneTable in database.Tables) { if (!oneTable.IsSystemObject) { if (matchOnNameContains == false || (matchOnNameContains == true && oneTable.Name.ToUpper().Contains(textToMatchOnNameContains))) { List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> oneTableObject = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>(); SqlSmoObject[] objectArray = { oneTable }; int depCount = CountTableDependancies(scripter, objectArray); if (depCount > 0) { ScriptMove.WriteToLog(string.Format("table {0} has {1} dependancies", oneTable.Name, depCount)); } oneTableObject.Add(oneTable.Urn); allTableObjects.Add(oneTable.Urn); string fileName = string.Format("{0}.{1}.{2}.sql", string.Format("0000{0}", tableIndex).Right(4), oneTable.Schema, oneTable.Name); string fullFileName = Path.Combine(tableDirectory, fileName); try { WriteScriptToFile(fullFileName, oneTable.Urn, ref scripter); } catch (Exception ex) { ScriptMove.WriteToLog(String.Format(" Unable to script {0} due to error {1}", oneTable.Name, ex.Message)); } tableIndex++; } } } ScriptMove.WriteToLog(String.Format("{0} tables scripted. Elapsed seconds: {1}", tableIndex, blockStart.Elapsed.TotalSeconds)); }
public static void ScriptDatabaseTriggers( Database database, string scriptDirectory, Microsoft.SqlServer.Management.Smo.Scripter scripter, bool matchOnNameContains, string textToMatchOnNameContains) { string triggerDirectory = Path.Combine(scriptDirectory, "ddltriggers"); System.IO.Directory.CreateDirectory(triggerDirectory); Stopwatch blockStart = new Stopwatch(); blockStart.Start(); DatabaseDdlTriggerCollection allTriggers = database.Triggers; List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> allTriggerObjects = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>(); int triggerIndex = 0; ScriptMove.WriteToLog("Scripting DDL triggers..."); foreach (DatabaseDdlTrigger oneTrigger in allTriggers) { if (!oneTrigger.IsSystemObject) { if (matchOnNameContains == false || (matchOnNameContains == true && oneTrigger.Name.ToUpper().Contains(textToMatchOnNameContains))) { List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> oneTriggerObject = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>(); oneTriggerObject.Add(oneTrigger.Urn); string fileName = string.Format("{0}.{1}.{2}.sql", string.Format("0000{0}", triggerIndex).Right(4), "dbo", oneTrigger.Name); string fullFileName = Path.Combine(triggerDirectory, fileName); try { WriteScriptToFile(fullFileName, oneTrigger.Urn, ref scripter); } catch (Exception ex) { ScriptMove.WriteToLog(String.Format(" Unable to script {0} due to error {1}", oneTrigger.Name, ex.Message)); } triggerIndex++; } } } ScriptMove.WriteToLog(String.Format("{0} DDL triggers scripted. Elapsed seconds: {1}", triggerIndex, blockStart.Elapsed.TotalSeconds)); }
private void ScriptToFile( string scriptDirectory) { Stopwatch totalTime = new Stopwatch(); totalTime.Start(); string nowDir = string.Format("{0}_{1}_{2}_{3}_{4}", DateTime.Now.Year, string.Format("00{0}", DateTime.Now.Month).Right(2), string.Format("00{0}", DateTime.Now.Day).Right(2), string.Format("00{0}", DateTime.Now.Hour).Right(2), string.Format("00{0}", DateTime.Now.Minute).Right(2)); string instanceScriptPath = Path.Combine(scriptDirectory, nowDir); Directory.CreateDirectory(instanceScriptPath); _logPath = Path.Combine(instanceScriptPath, "log.txt"); Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(_serverName); if (_useIntegratedAuthentication) { server.ConnectionContext.LoginSecure = true; } else { server.ConnectionContext.LoginSecure = false; server.ConnectionContext.Login = _userId; server.ConnectionContext.Password = _password; } Microsoft.SqlServer.Management.Smo.Database database = server.Databases[_databaseName]; /* define scripting options */ ScriptingOptions options = new ScriptingOptions(); options.AllowSystemObjects = false; options.AllowSystemObjects = false; options.IncludeDatabaseContext = false; options.IncludeIfNotExists = false; options.ClusteredIndexes = true; options.Default = true; options.DriAll = true; options.Indexes = true; options.NonClusteredIndexes = true; options.IncludeHeaders = true; options.ToFileOnly = true; options.AppendToFile = true; options.AnsiPadding = false; // Set scripter options to ensure only schema is scripted options.ScriptSchema = true; options.ScriptData = false; //Exclude GOs after every line options.NoCommandTerminator = false; Microsoft.SqlServer.Management.Smo.Scripter scripter = new Microsoft.SqlServer.Management.Smo.Scripter(); scripter.Options = options; scripter.Server = server; if (_includeTables) { ExportScript.ScriptTables(database, instanceScriptPath, scripter, _matchOnNameContains, _textToMatchOnNameContains); } if (_includeViews) { ExportScript.ScriptViews(database, instanceScriptPath, scripter, _matchOnNameContains, _textToMatchOnNameContains); } if (_includeFunctions) { ExportScript.ScriptFunctions(database, instanceScriptPath, scripter, _matchOnNameContains, _textToMatchOnNameContains); } if (_includeProcedures) { ExportScript.ScriptProcedures(database, instanceScriptPath, scripter, _matchOnNameContains, _textToMatchOnNameContains); } if (_includeTriggers) { ExportScript.ScriptTableTriggers(database, instanceScriptPath, scripter, _matchOnNameContains, _textToMatchOnNameContains); } if (_includeDDLTriggers) { ExportScript.ScriptDatabaseTriggers(database, instanceScriptPath, scripter, _matchOnNameContains, _textToMatchOnNameContains); } WriteToLog(String.Format("Scripting completed. Elapsed seconds: {0}", totalTime.Elapsed.TotalSeconds)); }