public void ExportData(string connectionString, string outputDirectory, bool verbose)
{
ScriptingOptions _options = new ScriptingOptions()
{
AllowSystemObjects = false,
ScriptData = true,
ScriptSchema = false
};
// connection
using (SqlConnection _connection = new SqlConnection(connectionString))
{
ServerConnection _serverConn = new ServerConnection(_connection);
Server _server = new Server(_serverConn);
_server.SetDefaultInitFields(typeof(Table), "IsSystemObject");
Database _db = _server.Databases[_connection.Database];
Scripter _scripter = new Scripter(_server);
_scripter.Options = _options;
foreach (Table _table in _db.Tables)
{
if (!_table.IsSystemObject)
{
string _outputPath = this.CreateOutputFile(outputDirectory, _table);
this.ScriptTable(_scripter, _table, _outputPath, false);
}
}
}
}
public void FilterTest()
{
List <string> boldedTexts = new List <string>()
{
"In 1929", "In 1949", "In 1950,", "In 1955,"
};
string path = Assembly.GetExecutingAssembly().UnpackResourceToFile("Resources." + nameof(Resources.document2), "jpg");
Scripter scripter = new Scripter();
scripter.InitVariables.Clear();
scripter.Text = ($@"ocrabbyy.processfile {SpecialChars.Text}{path}{SpecialChars.Text}
ocrabbyy.filter filter bold");
scripter.Run();
List <GStruct.Structure> res = scripter.Variables.GetVariableValue <List <GStruct.Structure> >("result");
foreach (GStruct.Structure value in res)
{
string text = ((GStruct.TextStructure)value).Value.Trim();
if (boldedTexts.Contains(text))
{
boldedTexts.Remove(text);
}
}
System.Text.StringBuilder notRecognizedBold = new System.Text.StringBuilder();
foreach (string s in boldedTexts)
{
notRecognizedBold.Append($" '{s}'");
}
Assert.AreEqual(0, boldedTexts.Count, $"Text not recognized as boold {notRecognizedBold}");
}
private void WhereUsedMenuItem_Click(object sender, EventArgs e)
{
TreeNode node;
TreeNode newNode = new TreeNode();
Urn[] urns = new Urn[1];
Scripter scripter;
// Get selected node
node = this.DependenciesTreeView.SelectedNode;
// Only do this once
if (node.Nodes.ContainsKey(Properties.Resources.UsedBy) == true
| node.Name == Properties.Resources.UsedBy)
{
return;
}
// Get the urn from the node
urns[0] = (Urn)(node.Tag);
// Add a "where used" node
newNode = new TreeNode(Properties.Resources.UsedBy);
newNode.Name = Properties.Resources.UsedBy;
node.Nodes.Add(newNode);
// And add the tree to the current node
scripter = new Scripter(server);
AddChildren(newNode, scripter.DiscoverDependencies(urns, DependencyType.Children).FirstChild);
node.Expand();
newNode.Expand();
}
//Генерация скриптов для представлений
private static void GenerateViewScript(Server myServer, string path)
{
Directory.CreateDirectory(path + @"\Views\");
string text = "";
Scripter scripter = new Scripter(myServer);
Database myAdventureWorks = myServer.Databases[DBName];
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptDrops = false;
scriptOptions.IncludeIfNotExists = false;
scriptOptions.Encoding = System.Text.Encoding.GetEncoding(1251);
foreach (Microsoft.SqlServer.Management.Smo.View myView in myAdventureWorks.Views)
{
if (myView.IsSystemObject)
{
continue;
}
StringCollection ProcedureScripts = myView.Script(scriptOptions);
string newSql = "";
foreach (string script in ProcedureScripts)
{
newSql = newSql + script + newline;
text = text + script + newline;
}
File.WriteAllText(path + @"\Views\" + myView.Name + ".sql", newSql, System.Text.Encoding.GetEncoding(1251));
}
File.WriteAllText(path + @"\" + "AllView.sql", text, System.Text.Encoding.GetEncoding(1251));
}
private static void ScriptUserDefinedFunctions(Action <string> logAction, Scripter scripter, Database database, DirectoryInfo dbDirectory)
{
//create object level folder for tables
var objectTypeDirectory = CreateObjectTypeFolder(logAction, dbDirectory, "Functions");
scripter.Options.ScriptDrops = false;
scripter.Options.DriAllConstraints = false;
scripter.Options.IncludeIfNotExists = false;
scripter.Options.IncludeDatabaseContext = false;
foreach (UserDefinedFunction dbObject in database.UserDefinedFunctions)
{
//do not script system objects
if (dbObject.IsSystemObject)
{
continue;
}
var objectScriptLineCollection = scripter.Script(new Urn[] { dbObject.Urn });
WriteScriptLineCollectionToFile(objectTypeDirectory, "FUNCTION", dbObject.Schema, dbObject.Name, objectScriptLineCollection, database.Name);
logAction(string.Format("Scripted user defined function : {0}.{1}", dbObject.Schema, dbObject.Name));
}
}
static void Main(string[] args)
{
Server srv = new Server(@"SERVER");
Database db = srv.Databases["DB"];
Scripter stpsc = new Scripter(srv);
// This option will script the tables with the procedures
stpsc.Options.WithDependencies = false;
foreach (StoredProcedure stp in db.StoredProcedures)
{
if (stp.IsSystemObject == false)
{
Console.WriteLine("/* Create script for procedure " + stp.Name + " */");
System.Collections.Specialized.StringCollection sc2 = stpsc.Script(new Urn[] { stp.Urn });
foreach (string sp in sc2)
{
Console.WriteLine("Migrating procedure " + stp.Name + "... ");
using (var scon = Connections.Connect())
{
SqlCommand runit = new SqlCommand(sp, scon);
runit.ExecuteNonQuery();
runit.Dispose();
scon.Close();
}
}
Console.WriteLine("\n");
}
}
}
/// <summary>
/// ユーザー定義関数のスクリプト作成
/// </summary>
/// <param name="scrp">出力するスクリプトの設定</param>
/// <param name="db">データベース</param>
/// <param name="csvDataList">CSV情報</param>
/// <param name="appendFile">追記指示</param>
private static void CreateScriptUserDefinedFunction(Scripter scrp, Database db, List <CsvData> csvDataList, bool appendFile)
{
// F = ユーザー定義関数 の場合
var objectTypeName = "UserDefinedFunction";
foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
{
if (udf.IsSystemObject)
{
// システムオブジェクトの場合は、対象外
continue;
}
// 対象オブジェクト情報を設定
var targetObjectInfo = new CsvData();
targetObjectInfo.OwnerName = udf.Owner;
targetObjectInfo.ObjectName = udf.Name;
if (!csvDataList.Any(x => x.ObjectName == targetObjectInfo.ObjectName &&
x.OwnerName == targetObjectInfo.OwnerName))
{
// CSVに該当データなし
continue;
}
// スクリプト情報を取得
var sc = scrp.Script(new Urn[] { udf.Urn });
// スクリプト作成
CreateScript(db.Name, objectTypeName, targetObjectInfo, sc, appendFile);
}
}
private static void ScriptTables(Action <string> logAction, Scripter scripter, Database database, DirectoryInfo dbDirectory)
{
//create object level folder for tables
var objectTypeDirectory = CreateObjectTypeFolder(logAction, dbDirectory, "Tables");
scripter.Options.ScriptDrops = false;
scripter.Options.DriAllConstraints = false;
scripter.Options.IncludeIfNotExists = false;
scripter.Options.IncludeDatabaseContext = false;
foreach (Table table in database.Tables)
{
//do not script system objects
if (table.IsSystemObject)
{
continue;
}
var objectScriptLineCollection = scripter.Script(new Urn[] { table.Urn });
WriteScriptLineCollectionToFile(objectTypeDirectory, "TABLE", table.Schema, table.Name, objectScriptLineCollection, database.Name);
logAction(string.Format("Scripted table : {0}.{1}", table.Schema, table.Name));
}
}
static void Main(string[] args)
{
const string databaseName = "MyDatabase";
const string scriptFile = @"D:\MyDatabase.sql";
var server = new Server(new ServerConnection
{
ConnectionString = new SqlConnectionStringBuilder
{
DataSource = @"LOCALHOST\SQLEXPRESS",
IntegratedSecurity = true
}.ToString()
});
server.ConnectionContext.Connect();
var database = server.Databases[databaseName];
var output = new StringBuilder();
foreach (Table table in database.Tables)
{
var scripter = new Scripter(server)
{
Options = { ScriptData = true }
};
var script = scripter.EnumScript(new SqlSmoObject[] { table });
foreach (var line in script)
{
output.AppendLine(line);
}
}
File.WriteAllText(scriptFile, output.ToString());
}
/// <summary>
/// ビューのスクリプト作成
/// </summary>
/// <param name="scrp">出力するスクリプトの設定</param>
/// <param name="db">データベース</param>
/// <param name="csvDataList">CSV情報</param>
/// <param name="appendFile">追記指示</param>
private static void CreateScriptView(Scripter scrp, Database db, List <CsvData> csvDataList, bool appendFile)
{
// V = ビュー の場合
var objectTypeName = "View";
foreach (View vw in db.Views)
{
if (vw.IsSystemObject)
{
// システムオブジェクトの場合は、対象外
continue;
}
// 対象オブジェクト情報を設定
var targetObjectInfo = new CsvData();
targetObjectInfo.OwnerName = vw.Owner;
targetObjectInfo.ObjectName = vw.Name;
if (!csvDataList.Any(x => x.ObjectName == targetObjectInfo.ObjectName &&
x.OwnerName == targetObjectInfo.OwnerName))
{
// CSVに該当データなし
continue;
}
// スクリプト情報を取得
var sc = scrp.Script(new Urn[] { vw.Urn });
// スクリプト作成
CreateScript(db.Name, objectTypeName, targetObjectInfo, sc, appendFile);
}
}
public static void Main()
{
String dbName = "MonoX105"; // database name
// Connect to the local, default instance of SQL Server.
Server srv = new Server();
// Reference the database.
Database db = srv.Databases[dbName];
// 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
// Iterate through the tables in database and script each one. Display the script.
foreach (Table tb in db.Tables)
{
// check if the table is not a system table
if (tb.IsSystemObject == false)
{
Console.WriteLine("-- Scripting for table " + tb.Name);
// Generating script for table tb
System.Collections.Specialized.StringCollection sc = scrp.Script(new Urn[] { tb.Urn });
foreach (string st in sc)
{
Console.WriteLine(st);
}
Console.WriteLine("--");
}
}
}
public void Backup()
{
foreach (Database database in server.Databases)
{
Scripter scripter = new Scripter(server);
scripter.Options.ScriptDrops = false;
scripter.Options.Indexes = true;
scripter.Options.DriAllConstraints = true;
scripter.Options.IncludeDatabaseRoleMemberships = true;
scripter.Options.Indexes = true;
scripter.Options.Permissions = true;
String script = "";
StringCollection stringCollection;
foreach (Table table in database.Tables)
{
if (table.IsSystemObject == false)
{
stringCollection = scripter.Script(new Urn[] { table.Urn });
foreach (string s in stringCollection)
{
script += s + Environment.NewLine;
}
}
TextWriter tw = new StreamWriter(dirPath + database.Name + @".sql");
tw.Write(script);
tw.Close();
}
}
}
public string ScriptDatabase()
{
var sb = new StringBuilder();
var server = new Server(@"DICKSON");
var databse = server.Databases["NYSCFileRecord"];
var scripter = new Scripter(server);
scripter.Options.ScriptDrops = false;
scripter.Options.WithDependencies = true;
scripter.Options.IncludeHeaders = true;
//And so on ....
var smoObjects = new Urn[1];
foreach (Microsoft.SqlServer.Management.Smo.Table t in databse.Tables)
{
smoObjects[0] = t.Urn;
if (t.IsSystemObject == false)
{
StringCollection sc = scripter.Script(smoObjects);
foreach (var st in sc)
{
sb.Append(st);
}
}
}
return(sb.ToString());
}
public void Init()
{
scripter = new Scripter();
scripter.InitVariables.Clear();
scripter.InitVariables.Add("fileId1", new TextStructure(FileID1));
scripter.InitVariables.Add("fileId2", new TextStructure(FileID2));
}
static void Main(string[] args)
{
const string dbName = "RealizeDemo300";
var server = new Server();
var database = server.Databases[dbName];
var scripter = new Scripter(server)
{
Options =
{
ScriptData = true,
ScriptSchema = false
}
};
foreach (Table databaseTable in database.Tables)
{
var scriptStringCollection = scripter.EnumScript(new []
{
databaseTable.Urn
});
foreach (var str in scriptStringCollection)
{
Console.WriteLine(str);
}
}
}
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 bool Save(Microsoft.AnalysisServices.Server server, OutputContext output, IMajorObject obj)
{
var builder = new StringBuilder();
using (var xmlWriter = XmlWriter.Create(builder, new XmlWriterSettings {
OmitXmlDeclaration = true
})) {
Scripter.WriteAlter(xmlWriter, obj, true, true);
xmlWriter.Flush();
}
var command = builder.ToString();
output.Debug(() => command);
var results = server.Execute(command);
if (results.Count > 0)
{
foreach (XmlaResult result in results)
{
if (result.Messages.Count > 0)
{
foreach (XmlaMessage message in result.Messages)
{
output.Error(message.Description);
}
return(false);
}
}
}
return(true);
}
/// <summary>
/// Take a screenshot and save it to a given path.
/// </summary>
/// <param name="path">Savepath.</param>
public static void Capture(string path)
{
int w, h;
Screen[] screens = Screen.AllScreens;
w = screens.ToList().Sum(i => i.Bounds.Width);
h = screens.ToList().Max(i => i.Bounds.Height);
Bitmap screen = new Bitmap(w, h, PixelFormat.Format32bppArgb);
Graphics gfx = Graphics.FromImage(screen);
gfx.CopyFromScreen(0, 0, 0, 0, new Size(w, h), CopyPixelOperation.SourceCopy);
try
{
if (String.IsNullOrEmpty(path))
{
path = $@"{DateTime.Today.Day}_{DateTime.Today.Month}-{DateTime.Now.Hour}_{DateTime.Now.Minute}_{DateTime.Now.Second}.png";
}
screen.Save($@"Data/User/{path}", AImageFormat.Png);
}
catch
{
Scripter.Execute($"error('Invalid path for screen.capture()! [Data/User/{path}]')");
}
gfx.Dispose();
screen.Dispose();
}
/// <summary>
/// Event handler for where used menu item.
/// Adds a "USED BY" node and performs a dependency discovery after which
/// all dependents are added to the "USED BY" node.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void WhereUsedToolStripMenuItem_Click(object sender, EventArgs e)
{
TreeNode node, newNode;
node = DependenciesTreeView.SelectedNode;
if (node.Nodes.ContainsKey(Properties.Resources.UsedBy) ||
node.Name == Properties.Resources.UsedBy)
{
return;
}
newNode = new TreeNode(Properties.Resources.UsedBy);
newNode.Name = Properties.Resources.UsedBy;
node.Nodes.Add(newNode);
// Advanced Scripting
Scripter scripter = new Scripter(this.sqlServerSelection);
Urn[] urns = new Urn[1];
urns[0] = (Urn)node.Tag;
// Discover dependents
DependencyTree tree = scripter.DiscoverDependencies(urns, DependencyType.Children);
// Add to tree (recursive)
AddChildren(newNode, tree.FirstChild);
node.Expand();
newNode.Expand();
}
private void CreateScripter()
{
_scripter = new Scripter(_owner.Server);
_scripter.Options.SetTargetDatabaseEngineType((DatabaseEngineType)Enum.Parse(typeof(DatabaseEngineType), _options.TargetDatabaseEngineType));
_scripter.Options.SetTargetServerVersion(new ServerVersion(_options.TargetServerVersion.Major, _options.TargetServerVersion.Minor));
_scripter.Options.AllowSystemObjects = false;
_scripter.Options.WithDependencies = false;
_scripter.Options.SchemaQualify = true;
_scripter.Options.SchemaQualifyForeignKeysReferences = true;
_scripter.Options.DriIncludeSystemNames = true;
_scripter.Options.IncludeIfNotExists = true;
_scripter.Options.DriClustered = true;
_scripter.Options.ClusteredIndexes = true;
_scripter.Options.IncludeHeaders = _options.IncludeHeaders;
_scripter.Options.AnsiPadding = _options.AnsiPadding;
_scripter.Options.NoFileGroup = _options.NoFileGroup;
_scripter.Options.NoCollation = _options.NoCollation;
_scripter.Options.NoExecuteAs = _options.NoExecuteAs;
_scripter.Options.NoFileStream = _options.NoFileStream;
_scripter.Options.NoFileStreamColumn = _options.NoFileStreamColumn;
_scripter.Options.NoIdentities = _options.NoIdentities;
_scripter.Options.NoIndexPartitioningSchemes = _options.NoIndexPartitioningSchemes;
_scripter.Options.NoTablePartitioningSchemes = _options.NoTablePartitioningSchemes;
_scripter.Options.NoVardecimal = _options.NoVardecimal;
_scripter.Options.NoViewColumns = _options.NoViewColumns;
_scripter.Options.DriDefaults = _options.DriDefaults;
_scripter.Options.DriChecks = _options.DriChecks;
_scripter.Options.DriWithNoCheck = _options.DriWithNoCheck;
_scripter.Options.ExtendedProperties = _options.ExtendedProperties;
_scripter.Options.Triggers = _options.Triggers;
}
private bool GenerateDbObject(Scripter scripter,
ScriptSchemaObjectBase e,
DirectoryInfo targetDir,
string locationPart)
{
var name = e.Name;
var schema = e.Schema;
var urn = new[] { e.Urn };
var table = e as Table;
var view = e as View;
if ((table != null && table.IsSystemObject) ||
(view != null && view.IsSystemObject)
)
{
return(false);
}
var sc = scripter.Script(urn);
var sb = new StringBuilder();
foreach (var st in sc)
{
sb.Append(" ");
sb.Append(st);
}
var value = sb.ToString().Trim(new[] { '\r', '\n' });
var location = $"{targetDir.FullName}\\{locationPart}\\{schema}.{name}.sql";
System.IO.File.WriteAllText(location, value);
return(true);
}
/// <summary>
/// テーブルのスクリプト作成
/// </summary>
/// <param name="scrp">出力するスクリプトの設定</param>
/// <param name="db">データベース</param>
/// <param name="csvDataList">CSV情報</param>
/// <param name="appendFile">追記指示</param>
private static void CreateScriptTable(Scripter scrp, Database db, List <CsvData> csvDataList, bool appendFile)
{
// U = テーブル(ユーザー定義) の場合
var objectTypeName = "Table";
foreach (Table tb in db.Tables)
{
if (tb.IsSystemObject)
{
// システムオブジェクトの場合は、対象外
continue;
}
// 対象オブジェクト情報を設定
var targetObjectInfo = new CsvData();
targetObjectInfo.OwnerName = tb.Owner;
targetObjectInfo.ObjectName = tb.Name;
if (!csvDataList.Any(x => x.ObjectName == targetObjectInfo.ObjectName &&
x.OwnerName == targetObjectInfo.OwnerName))
{
// CSVに該当データなし
continue;
}
// スクリプト情報を取得
var sc = scrp.Script(new Urn[] { tb.Urn });
Console.WriteLine(tb.Name);
// スクリプト作成
CreateScript(db.Name, objectTypeName, targetObjectInfo, sc, appendFile);
}
}
public static void Main(string[] args)
{
// Connect to the local, default instance of SQL Server.
Smo.Server srv = new Smo.Server();
// database name
Console.WriteLine("Enter database name for scripting:");
string dbName = Console.ReadLine();
// Reference the database.
Database db = srv.Databases[dbName];
// Define a Scripter object and set the required scripting options.
Scripter scripter = new Scripter(srv);
scripter.Options.ScriptDrops = false;
// To include indexes
scripter.Options.Indexes = true;
// to include referential constraints in the script
scripter.Options.DriAllConstraints = true;
// Iterate through the tables in database and script each one. Display the script.
foreach (Table tb in db.Tables)
{
// check if the table is not a system table
if (tb.IsSystemObject == false)
{
Console.WriteLine("-- Scripting for table " + tb.Name);
// Generating script for table tb
System.Collections.Specialized.StringCollection sc = scripter.Script(new Urn[] { tb.Urn });
foreach (string st in sc)
{
Console.WriteLine(st);
}
Console.WriteLine("--");
}
}
}
/// <summary>
/// ストアド プロシージャのスクリプト作成
/// </summary>
/// <param name="scrp">出力するスクリプトの設定</param>
/// <param name="db">データベース</param>
/// <param name="csvDataList">CSV情報</param>
/// <param name="appendFile">作成するスクリプトの種別</param>
private static void CreateScriptStoredProcedure(Scripter scrp, Database db, List <CsvData> csvDataList, bool appendFile)
{
// P = SQL ストアド プロシージャ の場合
var objectTypeName = "StoredProcedure";
foreach (StoredProcedure sp in db.StoredProcedures)
{
if (sp.IsSystemObject)
{
// システムオブジェクトの場合は、対象外
continue;
}
// 対象オブジェクト情報を設定
var targetObjectInfo = new CsvData();
targetObjectInfo.OwnerName = sp.Owner;
targetObjectInfo.ObjectName = sp.Name;
if (!csvDataList.Any(x => x.ObjectName == targetObjectInfo.ObjectName &&
x.OwnerName == targetObjectInfo.OwnerName))
{
// CSVに該当データなし
continue;
}
// スクリプト情報を取得
var sc = scrp.Script(new Urn[] { sp.Urn });
// スクリプト作成
CreateScript(db.Name, objectTypeName, targetObjectInfo, sc, appendFile);
}
}
private string ScriptLinkedServers(string outputFilePath, string filePrefix)
{
string strRetVal = "";
if (!this.IncludeLinkedServers)
{
strRetVal = "Linked server scripts were not included.";
return(strRetVal);
}
try
{
Scripter scr = new Scripter(DBServer);
GetSystemObjectsProperty();
LinkedServer[] linkedServers = new LinkedServer[this.DBServer.LinkedServers.Count];
this.DBServer.LinkedServers.CopyTo(linkedServers, 0);
ScriptingOptions options = GetDBScriptingOptions(false);
options.FileName = GetFileName(this.DBServer.Name, outputFilePath, filePrefix);
scr.Options = options;
scr.Script(linkedServers);
strRetVal = "Susccessfully generated Linked server script for Server:" + this.DBServer.Name + " and saved to file:" + options.FileName;
}
catch (Exception ex)
{
strRetVal = "Error generating Linked server script for server:" + this.DBServer.Name + " Error: " + ex.Message;
if (ex.InnerException != null)
{
strRetVal = ex.InnerException.Message + System.Environment.NewLine + strRetVal;
}
}
return(strRetVal);
}
public override string GenerateSqlScript(bool includeData)
{
var serverConn = new ServerConnection((SqlConnection)connection);
server = new Server(serverConn);
var db = new Database(server, connection.Database);
List<Urn> list = new List<Urn>();
DataTable dataTable = db.EnumObjects(DatabaseObjectTypes.Table);
foreach (DataRow row in dataTable.Rows)
{
list.Add(new Urn((string)row["Urn"]));
}
Scripter scripter = new Scripter();
scripter.Server = server;
scripter.Options.IncludeDatabaseContext = false;
scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.ScriptData = includeData;
scripter.Options.SchemaQualifyForeignKeysReferences = true;
scripter.Options.NoCollation = true;
scripter.Options.DriAllConstraints = true;
scripter.Options.DriAll = true;
scripter.Options.DriAllKeys = true;
scripter.Options.Triggers = true;
scripter.Options.DriIndexes = true;
scripter.Options.ClusteredIndexes = true;
scripter.Options.NonClusteredIndexes = true;
scripter.Options.ToFileOnly = false;
var scripts = scripter.EnumScript(list.ToArray());
string result = "";
foreach (var script in scripts)
result += script + Environment.NewLine;
serverConn.Disconnect();
return result;
}
private void ScriptSchemas(Database db, Scripter scr)
{
if (!IncludeSchemas)
{
return;
}
List <Schema> lstSchema = new List <Schema>(1);
foreach (Schema smo in db.Schemas)
{
if (smo.Name == "sys" ||
smo.Name == "dbo" ||
smo.Name == "db_accessadmin" ||
smo.Name == "db_backupoperator" ||
smo.Name == "db_datareader" ||
smo.Name == "db_datawriter" ||
smo.Name == "db_ddladmin" ||
smo.Name == "db_denydatawriter" ||
smo.Name == "db_denydatareader" ||
smo.Name == "db_owner" ||
smo.Name == "db_securityadmin" ||
smo.Name == "INFORMATION_SCHEMA" ||
smo.Name == "guest")
{
continue;
}
{
lstSchema.Add(smo);
}
}
scr.Script(lstSchema.ToArray());
}
public static void Script(this SqlInstance sqlInstance, string outputpath, Action <string> logAction)
{
var scripter = new Scripter(sqlInstance.Server);
var database = sqlInstance.Server.Databases[sqlInstance.SqlConnection.Database];
//add a database level folder
var dbDirectory = Directory.CreateDirectory(Path.Combine(outputpath, database.Name));
logAction(string.Format("Created folder for database : {0}", dbDirectory.FullName));
if (database.Tables.Count > 0)
{
ScriptTables(logAction, scripter, database, dbDirectory);
}
if (database.StoredProcedures.Count > 0)
{
ScriptStoredProcedures(logAction, scripter, database, dbDirectory);
}
if (database.Views.Count > 0)
{
ScriptViews(logAction, scripter, database, dbDirectory);
}
if (database.UserDefinedFunctions.Count > 0)
{
ScriptUserDefinedFunctions(logAction, scripter, database, dbDirectory);
}
}
private void ScriptTypes(Database db, Scripter scr)
{
if (!IncludeTypes)
{
return;
}
List <UserDefinedDataType> lstObjects = new List <UserDefinedDataType>(10);
foreach (UserDefinedDataType obj in db.UserDefinedDataTypes)
{
lstObjects.Add(obj);
}
scr.Script(lstObjects.ToArray());
List <UserDefinedTableType> lstUserDefTableTypes = new List <UserDefinedTableType>(10);
foreach (UserDefinedTableType obj in db.UserDefinedTableTypes)
{
lstUserDefTableTypes.Add(obj);
}
scr.Script(lstUserDefTableTypes.ToArray());
List <UserDefinedType> lstUserDefinedTypes = new List <UserDefinedType>(10);
foreach (UserDefinedType obj in db.UserDefinedTypes)
{
lstUserDefinedTypes.Add(obj);
}
scr.Script(lstUserDefinedTypes.ToArray());
}
public static void Script(string[] target, Database db
, string output, bool progress, SqlServerVersion sql_version
, bool do_version)//.Version100)
{
if (null == db)
{
throw new ScripterException("Invalid Database");
}
_do_version = do_version;
Scripter scripter = new Scripter(db.Parent);
ScriptingOptions op = new ScriptingOptions
{
AllowSystemObjects = false
,
WithDependencies = false
, ClusteredIndexes = true
, Indexes = true
, DriAllConstraints = true
//,
//, DriAll = true
, TargetServerVersion = sql_version
};
System.Console.WriteLine("Target Version {0}", op.TargetServerVersion);
scripter.Options = op;
Script(target, db, scripter, output, progress);
}
private static void ConfigureScriptingSources(AppOptions options, Scripter scripter)
{
if (options.ScriptFunctions)
{
scripter.AddSources(
new ScriptingSource("Functions", (srvr, db) => srvr.GetUserDefinedFunctions(db))
);
}
if (options.ScriptStoredProcedures)
{
scripter.AddSources(
new ScriptingSource("Sprocs", (srvr, db) => srvr.GetStoredProcedures(db))
);
}
if (options.ScriptTables)
{
scripter.AddSources(
new ScriptingSource("Tables", (srvr, db) => srvr.GetTables(db))
);
}
if (options.ScriptViews)
{
scripter.AddSources(
new ScriptingSource("Views", (srvr, db) => srvr.GetViews(db))
);
}
}
IEnumerator cacAttackAnim(bool record)
{
canMove = false;
cacAttack.gameObject.SetActive(true);
cacAttack.ResetTrigger("CacAttack");
cacAttack.SetTrigger("CacAttack");
yield return(new WaitForSeconds(0.267f));
cacAttack.gameObject.SetActive(false);
if (record)
{
Scripter s = new Scripter();
s.Action = Actions.e_Actions.TeleportAttack;
s.ID = -1;
s.Time = tm.UpdateTimer;
gm.OriginalTimeMachine.Add(s);
}
yield return(new WaitForSeconds(0.2f));
// KILL THE PLAYER CREATE THE EVENT
if (record)
{
p.GetComponent <Controller>().EndRound();
}
canMove = true;
yield return(null);
}
private static void GenereateData(Database db, Scripter scripter, StringBuilder sbScript)
{
var tbls = new Table[db.Tables.Count];
db.Tables.CopyTo(tbls, 0);
var tree = scripter.DiscoverDependencies(tbls, true);
var depwalker = new DependencyWalker();
var depcoll = depwalker.WalkDependencies(tree);
var sb = new StringBuilder();
//progressBar1.Maximum = sviews.Count;
foreach (DependencyCollectionNode dep in depcoll)
{
var o = db.Tables[dep.Urn.GetAttribute("Name")];
if (o != null && !o.IsSystemObject)
{
sb.AppendFormat("EXEC sp_generate_inserts @table_name='{0}', @owner='dbo'{1}", dep.Urn.GetAttribute("Name"), Environment.NewLine);
}
}
DataSet dset = db.ExecuteWithResults(sb.ToString());
foreach (DataTable dt in dset.Tables)
{
foreach (DataRow dr in dt.Rows)
{
sbScript.AppendLine(dr[0].ToString());
}
}
}
private void TreeView_ItemDrag(object sender, ItemDragEventArgs e)
{
Tree_CurrentDragObject = new DataObject();
draggedNodes = UI.TreeView.SelectedNodes.Contains(UI.TreeView.CurrentNode) ? UI.TreeView.SelectedNodes.ToArray() : new[] { UI.TreeView.CurrentNode };
var scriptableObjects = new HashSet <ObjectType>()
{
ObjectType.Table, ObjectType.Role, ObjectType.DataSource, ObjectType.Partition
};
// Only generate TMSL script when dragging a single object:
if (draggedNodes.Length == 1 && UI.TreeView.CurrentNode.Tag is Model)
{
Tree_CurrentDragObject.SetData(Scripter.ScriptCreateOrReplace());
}
else if (draggedNodes.Length == 1 && UI.TreeView.CurrentNode.Tag is LogicalGroup)
{
Tree_CurrentDragObject.SetData(Serializer.SerializeObjects(UI.TreeView.CurrentNode.Children.Select(n => n.Tag).OfType <TabularObject>()));
}
else if (draggedNodes.Length == 1 && UI.TreeView.CurrentNode.Tag is TabularNamedObject && scriptableObjects.Contains((UI.TreeView.CurrentNode.Tag as TabularNamedObject).ObjectType))
{
Tree_CurrentDragObject.SetData(Scripter.ScriptCreateOrReplace(UI.TreeView.CurrentNode.Tag as TabularNamedObject));
}
else
{
Tree_CurrentDragObject.SetData(Serializer.SerializeObjects(Selection.OfType <TabularObject>()));
}
Tree_CurrentDragObject.SetData(draggedNodes);
UI.TreeView.DoDragDrop(Tree_CurrentDragObject, DragDropEffects.Move | DragDropEffects.Copy);
draggedNodes = null;
}
public void GenerateData(DbInfo dbInfo, TextWriter textWriter)
{
var serverConn = new ServerConnection(dbInfo.Server, dbInfo.UserId, dbInfo.Password);
Server srv = new Server(serverConn);
Database db = srv.Databases[dbInfo.DbName];
if (db == null)
{
throw new Exception($"Database {dbInfo.DbName} not exists.");
}
Scripter scripter = new Scripter(srv);
scripter.Options.ScriptData = true;
scripter.Options.ScriptSchema = false;
textWriter.WriteLine("-----------------------------------------");
textWriter.WriteLine("-----------------data--------------------");
textWriter.WriteLine("-----------------------------------------");
foreach (Urn table in getTableUrnWithDependence(db))
{
Console.WriteLine();
var sc = scripter.EnumScript(new Urn[] { table });
foreach (string st in sc)
{
Console.WriteLine(st);
textWriter.WriteLine(st);
}
if (sc.Count() > 0)
{
textWriter.WriteLine("GO");
}
}
}
static void Main(string[] args)
{
var server = new Server(new ServerConnection {
ConnectionString = new SqlConnectionStringBuilder {
DataSource = @"Your Server Name", UserID = "Your User Id", Password = "******"
}.ToString()
});
server.ConnectionContext.Connect();
var database = server.Databases["Your Database Name"];
using (FileStream fs = new FileStream(@"H:\database_scripts\Gaurav.sql", FileMode.Append, FileAccess.Write))
using (StreamWriter sw = new StreamWriter(fs))
{
for each (Table table in database.Tables)
{
if (table.Name == "Your Table Name")
{
var scripter = new Scripter(server)
{
Options = { ScriptData = true }
};
var script = scripter.EnumScript(new SqlSmoObject[] { table });
for each (string line in script)
{
sw.WriteLine(line);
Console.WriteLine(line);
}
}
}
}
}
public System.Collections.Generic.IReadOnlyList<TableDefinition> GetDefinitions(ISqlConnectionProvider sqlConnectionProvider)
{
var conn = sqlConnectionProvider.GetConnection();
var dbName = conn.Database;
var serverConnection = new ServerConnection(conn);
var server = new Server(serverConnection);
var scripter = new Scripter(server);
scripter.Options.ScriptDrops = false;
scripter.Options.WithDependencies = false;
scripter.Options.NoCollation = true;
var db = server.Databases[dbName];
var results = new List<TableDefinition>();
foreach (Table table in db.Tables)
{
if (table.IsSystemObject)
continue;
var id = table.ID;
var definitions = scripter.Script(new Urn[] {table.Urn});
var sb = new StringBuilder();
foreach (var definition in definitions)
{
sb.AppendLine(definition);
}
var flattened = sb.ToString();
results.Add(new TableDefinition(id, flattened));
}
return results;
}
public static IEnumerable<string> CreateKeys(this Database db)
{
var scr = new Scripter(GenServer);
var resultScript = new List<string> { Comment("Create Defaults and FK") };
//Defaults
scr.Options.DriDefaults = true;
scr.Options.IncludeIfNotExists = true;
foreach (var table in db.Tables.Cast<Table>().Where(x => x.IsSystemObject == false))
{
resultScript.AddRange(scr.Script(new[] { table.Urn }).Cast<string>());
}
//FK's
scr.Options.DriDefaults = false;
scr.Options.DriForeignKeys = true;
scr.Options.IncludeIfNotExists = true;
scr.Options.SchemaQualifyForeignKeysReferences = true;
foreach (var table in db.Tables.Cast<Table>().Where(x => x.IsSystemObject == false))
{
resultScript.AddRange(scr.Script(new[] { table.Urn }).Cast<string>());
}
#region Cleanings and Repairs
for (var i = 0; i < resultScript.Count; i++)
{
if (resultScript[i].Contains("CREATE TABLE"))
{
resultScript.RemoveAt(i);
--i;
}
else if (resultScript[i].Contains("SET ANSI_NULLS"))
{
resultScript.RemoveAt(i);
--i;
}
else if (resultScript[i].Contains("SET QUOTED_IDENTIFIER"))
{
resultScript.RemoveAt(i);
--i;
}
else if (resultScript[i].Contains("dbo.sysobjects"))
{
var regex = new Regex("ALTER TABLE (?<table_name>\\[.*\\]) ADD CONSTRAINT (?<default_name>\\[.*\\]) DEFAULT ");
var result = regex.Match(resultScript[i]);
var tableName = result.Groups["table_name"].Value;
var tableDefault = result.Groups["default_name"].Value;
var stringToAdd = string.Format("IF NOT EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'{0}') AND parent_object_id = OBJECT_ID(N'{1}')) \r\nBEGIN \r\n{2} \r\nEND \r\n", tableDefault, tableName, resultScript[i]);
resultScript.RemoveAt(i);
resultScript.Insert(i, stringToAdd);
}
}
#endregion
return resultScript;
}
public static IEnumerable<string> AddDatabaseContext(this Database db)
{
var scr = new Scripter(GenServer) {Options = {IncludeDatabaseContext = true}};
var resultScript = new List<string> { Comment("Adding database context") };
resultScript.AddRange(scr.Script(new[] {db.Tables[0].Urn}).Cast<string>());
resultScript.RemoveRange(2, resultScript.Count - 2);
return resultScript;
}
public string GenerateSchemaFor(string serverName, string databaseName)
{
server = new Server(serverName);
database = server.Databases[databaseName];
script = new Scripter(server);
scriptBuilder = new StringBuilder();
List<Urn> urns = new List<Urn>();
// Common options
//script.Options.WithDependencies = true;
script.Options.SchemaQualify = false;
script.Options.AllowSystemObjects = false;
script.Options.DriAll = true;
script.Options.IncludeDatabaseRoleMemberships = false;
script.Options.ScriptData = false;
script.Options.ScriptDrops = false;
script.Options.ScriptSchema = true;
script.Options.ScriptBatchTerminator = true;
script.Options.Indexes = true;
script.Options.DriAllConstraints = true;
// Drop database
script.Options.IncludeIfNotExists = true;
script.Options.ScriptDrops = true;
urns.Add(database.Urn);
AppendScriptsForObjects(urns);
script.Options.IncludeIfNotExists = false;
script.Options.ScriptDrops = false;
urns.Clear();
// Create database
urns.Add(database.Urn);
AppendScriptsForObjects(urns);
urns.Clear();
// Use database
scriptBuilder.AppendFormat("USE [{0}]\r\n", databaseName);
scriptBuilder.AppendLine("GO");
// Create tables
foreach (Table table in database.Tables)
urns.Add(table.Urn);
AppendScriptsForObjects(urns);
return scriptBuilder.ToString();
}
public ScriptingForm(Server srvr, SqlSmoObject smoObject)
{
InitializeComponent();
this.sqlServerSelection = srvr;
this.smoScripter = new Scripter(srvr);
// Generate the dependency tree
this.dependTree = this.smoScripter.DiscoverDependencies(new SqlSmoObject[] { smoObject }, true);
UpdateTree();
UpdateListAndScript();
if (Phase1TreeView.Nodes.Count > 0)
{
Phase1TreeView.Nodes[0].ExpandAll();
}
}
public List<InsertionScript> DumpTables(string serverName, string databaseName)
{
this.serverName = serverName;
this.databaseName = databaseName;
server = new Server(serverName);
database = server.Databases[databaseName];
script = new Scripter(server);
// Common options
script.Options.SchemaQualify = false;
script.Options.AllowSystemObjects = false;
script.Options.ScriptData = true;
script.Options.ScriptSchema = false;
script.Options.ScriptBatchTerminator = true;
List<InsertionScript> scripts = new List<InsertionScript>();
foreach (Table table in database.Tables)
scripts.Add(GetScriptForTable(table));
return scripts;
}
static void Main(string[] args)
{
Server srv = new Server(@"SERVER");
string dbName = "DBONE";
string delete = "Archive";
Database db = srv.Databases[dbName];
Database delDB = srv.Databases[delete];
foreach (Table dTab in delDB.Tables)
{
using (var scon = Connections.Connect())
{
SqlCommand drop = new SqlCommand("DROP TABLE " + dTab, scon);
drop.ExecuteNonQuery();
drop.Dispose();
scon.Close();
}
}
Scripter tabScr = new Scripter(srv);
foreach (Table tb in db.Tables)
{
if (tb.IsSystemObject == false)
{
System.Collections.Specialized.StringCollection sctab = tabScr.Script(new Urn[] { tb.Urn });
foreach (string stab in sctab)
{
using (var scon = Connections.Connect())
{
SqlCommand copyTables = new SqlCommand(stab, scon);
copyTables.ExecuteNonQuery();
copyTables.Dispose();
scon.Close();
}
}
}
}
}
private void Script(Action<Scripter, MajorObject[], XmlWriter, bool> scripterMethod)
{
if (this.OutputFile == null)
{
this.Log.LogError("OutputFilePath is required");
return;
}
AMO.Database db = this.server.Databases[this.DatabaseItem.ItemSpec];
string objectsToScriptMetadata = this.DatabaseItem.GetMetadata("ObjectsToScript");
string[] objectsToScriptNames = !string.IsNullOrEmpty(objectsToScriptMetadata) ? objectsToScriptMetadata.Split(new[] { ';' }) : new string[0];
using (FileStream fileStream = File.OpenWrite(this.OutputFile.GetMetadata("FullPath")))
{
XmlTextWriter xmlTextWriter = new XmlTextWriter(fileStream, Encoding.UTF8);
List<MajorObject> objectsToScript = new List<MajorObject>();
foreach (DataSourceView dataSourceView in db.DataSourceViews)
{
if (objectsToScriptNames.Length == 0 || objectsToScriptNames.Contains(dataSourceView.Name))
{
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Scripting DataSourceView: {0}", dataSourceView.Name));
objectsToScript.Add(dataSourceView);
}
}
foreach (Dimension dimension in db.Dimensions.Cast<Dimension>().Where(dimension => objectsToScriptNames.Length == 0 || objectsToScriptNames.Contains(dimension.Name)))
{
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Scripting Dimension: {0}", dimension.Name));
objectsToScript.Add(dimension);
}
foreach (Cube cube in db.Cubes.Cast<Cube>().Where(cube => objectsToScriptNames.Length == 0 || objectsToScriptNames.Contains(cube.Name)))
{
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Scripting Cube: {0}", cube.Name));
objectsToScript.Add(cube);
}
Scripter scripter = new Scripter();
scripterMethod(scripter, objectsToScript.ToArray(), xmlTextWriter, false);
xmlTextWriter.Flush();
}
}
private void SetScriptOptions(string destinationPath, Scripter scripter)
{
scripter.Options.AppendToFile = false;
scripter.Options.ContinueScriptingOnError = true;
scripter.Options.NoCommandTerminator = true;
scripter.Options.DriAll = true;
scripter.Options.DriDefaults = true;
scripter.Options.FileName = GetFileName(destinationPath, this.DatabaseName, ".sql");
scripter.Options.IncludeDatabaseContext = false;
scripter.Options.IncludeIfNotExists = false;
scripter.Options.ScriptData = true;
scripter.Options.ScriptDrops = false;
scripter.Options.ScriptSchema = true;
scripter.Options.TimestampToBinary = false;
scripter.Options.ToFileOnly = true;
scripter.Options.WithDependencies = true;
}
/// <summary>
/// Dump tables to stream.
/// </summary>
/// <param name="database">Database to write tables for</param>
private void dumpTables(Database database)
{
foreach (Table table in database.Tables) {
// Do not write system tables
if (table.IsSystemObject)
continue;
// Generate script for table
Scripter scripter = new Scripter(m_server.sqlServer);
//scripter.Options.ScriptData = true;
//StringCollection script = scripter.EnumScript(new Urn[]{table.Urn});
//writeScriptToStream(script);
}
}
/*******************************************************************************
* Script Views
*******************************************************************************/
void scriptViews(Database db, Scripter scrp, Urn[] urn)
{
string filename;
string vwPath = _dbPath + @"\Views";
Directory.CreateDirectory(vwPath);
foreach (View vw in db.Views)
{
// skip system views
if (vw.IsSystemObject)
{
continue;
}
urn[0] = vw.Urn;
scrp.Options.Indexes = false;
scrp.Options.Triggers = false;
scrp.Options.ScriptDrops = true; //include the drop statement prior to create
scrp.Options.ScriptSchema = true;
filename = vwPath + @"\" + scrub(vw.Schema) + "." + scrub(vw.Name) + ".view.sql";
Console.WriteLine(" View: " + vw.Schema + "." + vw.Name);
// script the view
//ScriptIt(urn, scrp, filename, true);
ScriptIt(urn, scrp, filename, false);
// Script View Indexes
string ndxPath = vwPath + @"..\..\Indexes";
Directory.CreateDirectory(ndxPath);
foreach (Index ndx in vw.Indexes)
{
Console.WriteLine(" Index: " + ndx.Name);
urn[0] = ndx.Urn;
filename = ndxPath + @"\" + scrub(vw.Schema) + "." + scrub(vw.Name)
+ "." + scrub(ndx.Name) + ".index.sql";
// script the index
ScriptIt(urn, scrp, filename);
}
// Script View Triggers
string trgPath = vwPath + @"\Triggers";
Directory.CreateDirectory(trgPath);
foreach (Trigger trg in vw.Triggers)
{
Console.WriteLine(" Trigger: " + trg.Name);
urn[0] = trg.Urn;
filename = trgPath + @"\" + scrub(vw.Schema) + "." + scrub(vw.Name)
+ "." + scrub(trg.Name) + ".trigger.sql";
// script the trigger with drop statement
ScriptIt(urn, scrp, filename);
}
}
}
/*******************************************************************************
* Script User Defined Functions
*******************************************************************************/
void scriptUserDefinedFunctions(Database db, Scripter scrp, Urn[] urn)
{
string filename;
string funcPath = _dbPath + @"\Functions";
Directory.CreateDirectory(funcPath);
scrp.Options.ScriptSchema = true;
foreach (UserDefinedFunction func in db.UserDefinedFunctions)
{
// skip system functions
if (func.IsSystemObject)
{
continue;
}
urn[0] = func.Urn;
filename = funcPath + @"\" + scrub(func.Schema) + "." + scrub(func.Name) + ".function.sql";
Console.WriteLine(" User Defined Function: " + func.Schema + "." + func.Name);
// script the function with drop statement
//ScriptIt(urn, scrp, filename, true);
ScriptIt(urn, scrp, filename, false);
}
}
/*******************************************************************************
* Script Users
*******************************************************************************/
void scriptUsers(Database db, Scripter scrp, Urn[] urn)
{
string filename;
string userPath = _dbPath + @"\runAfterCreateDatabase";
Directory.CreateDirectory(userPath);
scrp.Options.Permissions = true;
foreach (User user in db.Users)
{
// skip system procedures
if (user.IsSystemObject)
{
continue;
}
urn[0] = user.Urn;
filename = userPath + @"\" + scrub(user.Name) + ".user.sql";
Console.WriteLine(" User: " + user.Name);
ScriptIt(urn, scrp, filename, false);
}
}
public void ScriptTable(Scripter scripter, Table table, string outputPath, bool append)
{
// alternatively
// _scripter.Options.ToFileOnly = true;
// _scripter.Options.FileName = _fileName;
// _options.NoCommandTerminator = true;
// _scripter.EnumScript(new Urn[] { _table.Urn });
using (StreamWriter _writer = new StreamWriter(outputPath, append, this.Encoding))
{
int _row = 0;
foreach (string _scriptLine in scripter.EnumScript(new Urn[] { table.Urn }))
{
// FIXME: IDENTITY_INSERT ON ends with [space][cr+lf] when scripted by SMO
// but not when scripted by SSMS 10.50
string _trimmedScriptLine = _scriptLine.TrimEnd('\r', '\n', ' ');
if (_row > 0 && _row % this.CommandTerminatorInterval == 0)
{
_writer.WriteLine(Exporter.CommandTerminator);
if (this.PrintProcessedRecords)
{
int _processedRows = _row;
//FIXME: Find out why is this necessary
if (this.OffByOne)
{
_processedRows -= _row / this.CommandTerminatorInterval;
}
_writer.WriteLine(Exporter.PrintProcessedRecordsFormat, _processedRows);
}
}
_writer.WriteLine(_trimmedScriptLine);
_row++;
}
}
}
private static void GenereateData(Database db, Scripter scripter, StringBuilder sbScript)
{
var tbls = new Table[db.Tables.Count];
db.Tables.CopyTo(tbls, 0);
var tree = scripter.DiscoverDependencies(tbls, true);
var depwalker = new DependencyWalker();
var depcoll = depwalker.WalkDependencies(tree);
var sb = new StringBuilder();
//progressBar1.Maximum = sviews.Count;
foreach (DependencyCollectionNode dep in depcoll)
{
var o = db.Tables[dep.Urn.GetAttribute("Name")];
if (o != null && !o.IsSystemObject)
sb.AppendFormat("EXEC sp_generate_inserts @table_name='{0}', @owner='dbo'{1}", dep.Urn.GetAttribute("Name"), Environment.NewLine);
}
DataSet dset = db.ExecuteWithResults(sb.ToString());
foreach (DataTable dt in dset.Tables)
{
foreach (DataRow dr in dt.Rows)
{
sbScript.AppendLine(dr[0].ToString());
}
}
}
private string ScriptObject(Urn[] urns, Scripter scripter)
{
StringCollection sc = scripter.Script(urns);
var sb = new StringBuilder();
foreach (string str in sc)
{
sb.Append(str + Environment.NewLine + "GO" + Environment.NewLine + Environment.NewLine);
}
return sb.ToString();
}
private void bwCalculation_DoWork(object sender, DoWorkEventArgs e)
{
_workCompletedWithoutError = false;
var conn = new ServerConnection(ConnectionManager.GetInstance().GetSqlConnection());
//myLog.WriteEntry("--connection--"+conn.ToString());
try
{
_sbScript = new StringBuilder();
var server = new Server(conn);
//myLog.WriteEntry("--server initalized--" + server.ToString());
var db = server.Databases[AppSettings.DatabaseName];
//myLog.WriteEntry("--get db--" + db.ToString());
//myLog.WriteEntry("--sp contain check--" + db.StoredProcedures.Contains("sp_generate_inserts").ToString());
if (!db.StoredProcedures.Contains("sp_generate_inserts"))
{
// myLog.WriteEntry("--get into spgenerate and call SpGenerate--" );
try
{
db.ExecuteNonQuery(SpGenerate());
}
catch(Exception ex)
{
//myLog.WriteEntry("--error on excute SpGenerate()--"+ex.Message+"--trace--"+ex.StackTrace+"--spgenerate out--"+SpGenerate());
}
}
var scripter = new Scripter(server);
//myLog.WriteEntry("--scripter initalized--" + scripter.ToString());
scripter.ScriptingProgress += new ProgressReportEventHandler(ScriptingProgressEventHandler);
var so = new ScriptingOptions
{
IncludeIfNotExists = false,
IncludeHeaders = false,
Permissions = false,
ExtendedProperties = false,
ScriptDrops = false,
IncludeDatabaseContext = false,
NoCollation = false,
NoFileGroup = false,
NoIdentities = false,
TargetServerVersion = SqlServerVersion.Version90
};
scripter.Options = so;
SetProgressBarText(1);
SetProgressBarValue(0);
//myLog.WriteEntry("--scripter options set--" + scripter.Options.ToString());
this.Invoke(new MethodInvoker(() => progressBar1.Maximum = db.Tables.Count));
server.SetDefaultInitFields(typeof (Table), "IsSystemObject");
//myLog.WriteEntry("--init start looping tables--");
foreach (Table tb in db.Tables)
{
//myLog.WriteEntry("--start looping tables--" + tb.Name);
ProgressBarPerformStep();
if (!tb.IsSystemObject)
{
_sbScript.Append(ScriptObject(new Urn[] {tb.Urn}, scripter));
}
}
SetProgressBarValue(0);
SetProgressBarText(2);
this.Invoke(new MethodInvoker(() => progressBar1.Maximum = db.UserDefinedFunctions.Count));
server.SetDefaultInitFields(typeof (UserDefinedFunction), "IsSystemObject");
foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
{
ProgressBarPerformStep();
if (!udf.IsSystemObject)
{
_sbScript.Append(ScriptObject(new Urn[] {udf.Urn}, scripter));
}
}
SetProgressBarValue(0);
SetProgressBarText(3);
this.Invoke(new MethodInvoker(() => progressBar1.Maximum = db.StoredProcedures.Count));
server.SetDefaultInitFields(typeof (StoredProcedure), "IsSystemObject");
foreach (StoredProcedure sp in db.StoredProcedures)
{
ProgressBarPerformStep();
if (!sp.IsSystemObject)
{
_sbScript.Append(ScriptObject(new Urn[] {sp.Urn}, scripter));
}
}
SetProgressBarValue(0);
SetProgressBarText(4);
this.Invoke(new MethodInvoker(delegate
{
lblProgress.Text = "Script Views";
}));
server.SetDefaultInitFields(typeof (Microsoft.SqlServer.Management.Smo.View), "IsSystemObject");
List<SqlSmoObject> views = db.Views.Cast<View>().Cast<SqlSmoObject>().ToList();
var sviews = SortViews(db, views);
this.Invoke(new MethodInvoker(() => progressBar1.Maximum = sviews.Count));
foreach (var v in sviews)
{
ProgressBarPerformStep();
if (!v.IsSystemObject)
{
_sbScript.Append(ScriptObject(new Urn[] {v.Urn}, scripter));
}
}
SetProgressBarText(5);
this.Invoke(new MethodInvoker(() => lblProgress.Text = "Execute Generate Insert Data"));
GenereateData(db, scripter, _sbScript);
_workCompletedWithoutError = true;
}
catch (Exception err)
{
//myLog.WriteEntry("--/ /--" + err.ToString());
MessageBox.Show(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Disconnect();
}
}
public void scriptDB(string directory)
{
DateTime began = DateTime.Now;
_dbPath = generateScriptPath(directory);
Console.WriteLine("\n**********************************************");
Console.WriteLine("Scripting [" + _database + "] to " + _dbPath);
Console.WriteLine("**********************************************\n");
Server srvr = _connection.server(_database);
try
{
srvr.Initialize();
srvr.SetDefaultInitFields(true);
}
catch (Exception e)
{
Console.WriteLine("\nERROR: Connection to Server " + _connection.serverName + ", Database " + _database + " failed\n");
Console.WriteLine(e);
return;
}
Database db = srvr.Databases[_database];
if (db == null)
{
Console.WriteLine("\nERROR: Database " + _database + " does not exist\n");
return;
}
// set up SMO scripting objects
Urn[] urn = new Urn[1];
Scripter scrp = new Scripter(srvr);
// set common scipter options
scrp.Options.IncludeHeaders = false; // don't include header info in version-controlled scripts
scrp.Options.AppendToFile = false; // fresh file every time
scrp.Options.AnsiFile = true;
scrp.Options.ContinueScriptingOnError = true;
scrp.Options.PrimaryObject = true;
scrp.Options.SchemaQualify = true;
scrp.Options.ToFileOnly = true;
scrp.Options.ConvertUserDefinedDataTypesToBaseType = true;
// options that make the results more likely to be executable.
scrp.Options.ExtendedProperties = false; // don't want extra guff - just defailts please
scrp.Options.NoCollation = true; // don't include collation info - defaults are ok
scrp.Options.Permissions = false;
// scripting the objects
//scriptDatabase(db, scrp, urn);
scriptUsers(db, scrp, urn);
scriptTables(db, scrp, urn);
scriptViews(db, scrp, urn);
scriptStoredProcedures(db, scrp, urn);
scriptUserDefinedFunctions(db, scrp, urn);
// done!
DateTime ended = DateTime.Now;
Console.WriteLine("\n[" + _database + "] began: " + began.ToLongTimeString() + ", ended: " + ended.ToLongTimeString());
Console.WriteLine("Number of objects scripted: " + objectCount.ToString());
Console.WriteLine("\n[" + _database + "] done....");
}
private void ScriptTableDataNative(Table table, string dataDirectory, Server server)
{
if (!Directory.Exists(dataDirectory)) Directory.CreateDirectory(dataDirectory);
var fileName = Path.ChangeExtension(Path.Combine(dataDirectory, GetScriptFileName(table)), "sql");
var scripter = new Scripter(server)
{
Options =
{
ScriptData = true,
ScriptSchema = false
}
};
using (TextWriter writer = GetStreamWriter(fileName, false))
{
foreach (var script in scripter.EnumScript(new[] { table }))
{
writer.WriteLine(script);
}
}
}
/*******************************************************************************
* Script Tables
*******************************************************************************/
void scriptTables(Database db, Scripter scrp, Urn[] urn)
{
string filename;
string tblPath = _dbPath + @"\Up";
Directory.CreateDirectory(tblPath);
foreach (Table tbl in db.Tables)
{
// skip system tables
if (tbl.IsSystemObject)
{
continue;
}
urn[0] = tbl.Urn;
scrp.Options.DriAll = false;
scrp.Options.Indexes = false;
scrp.Options.Triggers = false;
scrp.Options.NoFileGroup = false;
scrp.Options.DriForeignKeys = false;
scrp.Options.NoTablePartitioningSchemes = false;
//Script Tables
filename = tblPath + @"\" + scrub(tbl.Schema) + "." + scrub(tbl.Name) + ".table.sql";
Console.WriteLine(" Table: " + tbl.Schema + "." + tbl.Name);
// script the table
ScriptIt(urn, scrp, filename);
// permissions
string command = "EXEC sp_helprotect"
+ " @name = '" + tbl.Name + "'"
+ ", @grantorname = '" + tbl.Schema + "'";
ScriptPermissions(_connection.connectionString(), command, filename);
// Script Table Indexes
string keyPath = tblPath + @"\Keys";
Directory.CreateDirectory(keyPath);
string ndxPath = tblPath + @"..\..\Indexes";
Directory.CreateDirectory(ndxPath);
foreach (Index ndx in tbl.Indexes)
{
Console.WriteLine(" Index: " + ndx.Name);
urn[0] = ndx.Urn;
if (ndx.IndexKeyType.ToString() == "DriUniqueKey")
{
filename = keyPath + @"\" + scrub(tbl.Schema) + "." + scrub(tbl.Name)
+ "." + scrub(ndx.Name) + ".ukey.sql";
}
else if (ndx.IndexKeyType.ToString() == "DriPrimaryKey")
{
filename = keyPath + @"\" + scrub(tbl.Schema) + ".1." + scrub(tbl.Name)
+ "." + scrub(ndx.Name) + ".pkey.sql";
}
else
{
filename = ndxPath + @"\" + scrub(tbl.Schema) + "." + scrub(tbl.Name)
+ "." + scrub(ndx.Name) + ".index.sql";
}
// script the index
ScriptIt(urn, scrp, filename);
}
// Script Table Triggers
string trgPath = tblPath + @"\Triggers";
Directory.CreateDirectory(trgPath);
foreach (Trigger trg in tbl.Triggers)
{
Console.WriteLine(" Trigger: " + trg.Name);
urn[0] = trg.Urn;
filename = trgPath + @"\" + scrub(tbl.Schema) + "." + scrub(tbl.Name)
+ "." + scrub(trg.Name) + ".trigger.sql";
// script the trigger
ScriptIt(urn, scrp, filename);
}
// Script Check Constraints
string chkPath = tblPath + @"\Constraints";
Directory.CreateDirectory(chkPath);
scrp.Options.DriChecks = true;
foreach (Check chk in tbl.Checks)
{
Console.WriteLine(" Constraint: " + chk.Name);
urn[0] = chk.Urn;
filename = chkPath + @"\" + scrub(tbl.Schema) + "." + scrub(tbl.Name)
+ "." + scrub(chk.Name) + ".chkconst.sql";
// script the constraint
ScriptIt(urn, scrp, filename);
}
// Script Default Constraints
string defPath = chkPath;
scrp.Options.DriChecks = false;
foreach (Column col in tbl.Columns)
{
if (col.DefaultConstraint != null)
{
Console.WriteLine(" Constraint: " + col.DefaultConstraint.Name);
urn[0] = col.DefaultConstraint.Urn;
filename = defPath + @"\" + scrub(tbl.Schema) + "." + scrub(tbl.Name)
+ "." + scrub(col.DefaultConstraint.Name) + ".defconst.sql";
// script the constraint
ScriptIt(urn, scrp, filename);
}
}
// Script Foreign Keys
scrp.Options.DriForeignKeys = true;
scrp.Options.SchemaQualifyForeignKeysReferences = true;
foreach (ForeignKey fk in tbl.ForeignKeys)
{
Console.WriteLine(" Foreign Key: " + fk.Name);
urn[0] = fk.Urn;
filename = keyPath + @"\" + scrub(tbl.Schema) + "." + scrub(tbl.Name)
+ "." + scrub(fk.Name) + ".fkey.sql";
// script the constraint
ScriptIt(urn, scrp, filename);
}
}
}
private void ScriptData()
{
if (!this.VerifyDatabase())
{
return;
}
if (this.OutputFilePath == null)
{
this.Log.LogError("OutputFilePath is required");
return;
}
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Scripting Data for Database: {0} to: {1}", this.DatabaseItem.ItemSpec, this.OutputFilePath.GetMetadata("FullPath")));
Microsoft.SqlServer.Management.Smo.Database db = this.sqlServer.Databases[this.DatabaseItem.ItemSpec];
var scrp = new Scripter(this.sqlServer) { Options = { ScriptSchema = this.ScriptSchema, ScriptData = true, ScriptDrops = this.ScriptDrops } };
Regex filter = new Regex(this.RegexPattern, RegexOptions.Compiled);
if (File.Exists(this.OutputFilePath.GetMetadata("FullPath")))
{
File.Delete(this.OutputFilePath.GetMetadata("FullPath"));
System.Threading.Thread.Sleep(2000);
}
// Iterate through the tables in database and script each one. Display the script.
foreach (Table tb in db.Tables)
{
// check if the table is not a system table
if (tb.IsSystemObject == false && filter.IsMatch(tb.Name) && tb.RowCount > 0)
{
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "\tScripting: {0}. {1} rows", tb.Name, tb.RowCount));
var sc = scrp.EnumScript(new[] { tb.Urn });
System.IO.File.AppendAllLines(this.OutputFilePath.GetMetadata("FullPath"), sc);
}
}
}
/*******************************************************************************
* Script XML Schema Collections
*******************************************************************************/
void scriptXmlSchemaCollections(Database db, Scripter scrp, Urn[] urn)
{
string filename;
string xmlPath = _dbPath + @"\XML Schema Collections";
Directory.CreateDirectory(xmlPath);
foreach (XmlSchemaCollection xml in db.XmlSchemaCollections)
{
urn[0] = xml.Urn;
filename = xmlPath + @"\" + scrub(xml.Schema) + "." + scrub(xml.Name) + ".xmlschema.sql";
Console.WriteLine(" XML Schema Collection: " + xml.Schema + "." + xml.Name);
// script the xml schema collection
ScriptIt(urn, scrp, filename);
}
}
public void GenerateScriptFile(string destinationPath, Action<int> percentCompleteCallback, Action<Exception> errorCallback)
{
var server = GetDbServer();
var db = server.Databases[this.DatabaseName];
var scripter = new Scripter(server);
SetScriptOptions(destinationPath, scripter);
var smoObjects = new List<Urn>();
foreach (Table tb in db.Tables)
{
if (!tb.IsSystemObject)
{
smoObjects.Add(tb.Urn);
}
}
scripter.ScriptingError += new ScriptingErrorEventHandler((s, e) =>
{
if (errorCallback != null)
{
errorCallback(e.InnerException);
}
});
scripter.ScriptingProgress += new ProgressReportEventHandler((s, e) =>
{
int percent = Convert.ToInt32(((double)e.TotalCount / (double)e.Total) * 100.0);
if (percentCompleteCallback != null)
{
percentCompleteCallback(percent);
}
});
//var sc = scripter.Script(smoObjects.ToArray());
foreach (var sc in scripter.EnumScript(smoObjects.ToArray()))
{
}
}
/*******************************************************************************
* Script Stored Procedures
*******************************************************************************/
void scriptStoredProcedures(Database db, Scripter scrp, Urn[] urn)
{
string filename;
string procPath = _dbPath + @"\Sprocs";
Directory.CreateDirectory(procPath);
scrp.Options.Permissions = true;
foreach (StoredProcedure proc in db.StoredProcedures)
{
// skip system procedures
if (proc.IsSystemObject)
{
continue;
}
urn[0] = proc.Urn;
filename = procPath + @"\" + scrub(proc.Schema) + "." + scrub(proc.Name) + ".proc.sql";
Console.WriteLine(" Stored Procedure: " + proc.Schema + "." + proc.Name);
// script the procedure with drop statement
//ScriptIt(urn, scrp, filename, true);
ScriptIt(urn, scrp, filename, false);
}
}
