public static IEnumerable<string> CreateTablesWithData(this Database db)
{
var scr = new Scripter(GenServer);
var resultScript = new List<string> { Comment("Create tables + insert data") };
scr.Options.IncludeIfNotExists = true;
scr.Options.ScriptSchema = true;
scr.Options.DriPrimaryKey = true;
scr.Options.DriWithNoCheck = true;
scr.Options.DriUniqueKeys = true;
foreach (var table in db.Tables.Cast<Table>().Where(x => x.IsSystemObject == false))
{
resultScript.AddRange(scr.Script(new[] { table.Urn }).Cast<string>());
scr.Options.ScriptData = true;
scr.Options.ScriptSchema = false;
resultScript.AddRange(scr.EnumScript(new[] { table.Urn }));
scr.Options.ScriptData = false;
scr.Options.ScriptSchema = true;
}
return resultScript;
}
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 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 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 ScriptPrimaryKey(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;
string result = String.Empty;
for (int i = 0; i < tbl.Indexes.Count; i++)
{
if (tbl.Indexes[i].IndexKeyType == IndexKeyType.DriPrimaryKey)
{
Urn[] urn = new Urn[1];
urn[0] = tbl.Indexes[i].Urn;
result = CompactStrings(scripter.Script(urn));
break;
}
}
return(result);
}
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)));
}
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;
}
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());
}
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());
}
/// <summary>
/// Генерирует скрипты создания ролей и пишет их в <code>output</code>
/// </summary>
/// <param name="output">Дескриптор файла, в который записываются хранимые процедуры</param>
/// <param name="server">Сервер, на котором хранится база данных</param>
/// <param name="database">Контролируемая база данных</param>
private static void GenerateRoleScripts(XmlWriter output, Server server, Database database)
{
// Создали открывающийся тег
output.WriteStartElement("Roles");
foreach (DatabaseRole role in database.Roles)
{
if (role.Name == "public")
{
continue;
}
output.WriteElementString("Header", role.Name);
StringCollection strCollection = new StringCollection();
SqlSmoObject[] smoObj = new SqlSmoObject[1];
smoObj[0] = role;
Scripter scriptor = new Scripter(server);
scriptor.Options.AllowSystemObjects = false;
strCollection = scriptor.Script(smoObj);
output.WriteStartElement("script");
output.WriteString("\n");
foreach (string s in strCollection)
{
output.WriteString(s.Trim() + "\n");
}
output.WriteEndElement();
}
output.WriteEndElement();
}
/// <summary>
/// Генерирует скрипты создания индексов и пишет их в <code>output</code>
/// </summary>
/// <param name="output">Дескриптор файла, в который записываются хранимые процедуры</param>
/// <param name="server">Сервер, на котором хранится база данных</param>
/// <param name="database">Контролируемая база данных</param>
private static void GenerateIndexScripts(XmlWriter output, Database database)
{
// Создали открывающийся тег
output.WriteStartElement("Indexes");
foreach (Table table in database.Tables)
{
foreach (Index index in table.Indexes)
{
output.WriteElementString("Header", index.Name);
StringCollection strCollection = new StringCollection();
SqlSmoObject[] smoObj = new SqlSmoObject[1];
smoObj[0] = index;
Scripter scriptor = new Scripter(database.Parent);
scriptor.Options.AllowSystemObjects = false;
scriptor.Options.DriAll = true;
scriptor.Options.Default = true;
scriptor.Options.WithDependencies = false;
scriptor.Options.ScriptSchema = true;
strCollection = scriptor.Script(smoObj);
output.WriteStartElement("script");
output.WriteString("\n");
foreach (string s in strCollection)
{
output.WriteString(s.Trim() + "\n");
}
output.WriteEndElement();
}
}
output.WriteEndElement();
}
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");
}
}
}
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));
}
}
private void DeleteDependencies(IEnumerable <DependencyCollectionNode> dependencies)
{
Logger.WriteLine("Deleting dependencies:");
var script = new Scripter(_sourceServer)
{
Options = new ScriptingOptions
{
WithDependencies = true,
ScriptData = false,
ScriptDrops = true,
ScriptSchema = true,
IncludeIfNotExists = true
}
};
foreach (DependencyCollectionNode node in dependencies)
{
Urn urn = node.Urn;
Logger.WriteLine(" - Generating scripts from {0}", urn);
StringCollection scripts = script.Script(new[] { urn });
foreach (string scr in scripts)
{
Logger.WriteLine(" - Executing script on {0}: {1}", _targetDatabase.Urn, scr);
_targetDatabase.ExecuteNonQuery(scr);
}
}
}
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();
}
}
}
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>
/// Генерирует скрипты создания хранимых процедур и пишет их в <code>output</code>
/// </summary>
/// <param name="output">Дескриптор файла, в который записываются хранимые процедуры</param>
/// <param name="server">Сервер, на котором хранится база данных</param>
/// <param name="database">Контролируемая база данных</param>
private static void GenerateStoredProcScripts(XmlWriter output, Server server, Database database)
{
// Создали открывающийся тег
output.WriteStartElement("Stored-Procedures");
foreach (StoredProcedure proc in database.StoredProcedures)
{
if ((proc.Schema != "sys") && ((proc.Schema != "dbo") || (proc.Name.Substring(0, 2) != "sp")))
{
output.WriteElementString("Header", proc.Name);
StringCollection strCollection = new StringCollection();
SqlSmoObject[] smoObj = new SqlSmoObject[1];
smoObj[0] = proc;
Scripter scriptor = new Scripter(server);
scriptor.Options.AllowSystemObjects = false;
scriptor.Options.Indexes = true;
scriptor.Options.DriAll = true;
scriptor.Options.Default = true;
scriptor.Options.WithDependencies = true;
scriptor.Options.ScriptSchema = true;
strCollection = scriptor.Script(smoObj);
output.WriteStartElement("script");
output.WriteString("\n");
foreach (string s in strCollection)
{
output.WriteString(s.Trim() + "\n");
}
output.WriteEndElement();
}
}
output.WriteEndElement();
}
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));
}
}
private string getTableCreateSql(string connect, string name)
{
var sourceConn = new SqlConnection(connect);
Server srv = new Server(new ServerConnection(sourceConn));
//Server srv = new Server(".");
var dbName = sourceConn.Database;
// Reference the database.
Database db = srv.Databases[dbName];
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;
// Iterate through the tables in database and script each one. Display the script.
if (db.Tables.Contains(name))
{
System.Collections.Specialized.StringCollection sc = scrp.Script(new Urn[] { db.Tables[name].Urn });
//sql.AppendFormat("--script for table {0}\r\n", name);
foreach (string st in sc)
{
sql.AppendFormat("{0}\r\n", st);
}
//sql.Append("--\r\n");
}
sourceConn.Dispose();
return(sql.ToString());
}
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("--");
}
}
}
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());
}
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);
}
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 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("--");
}
}
}
/// <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);
}
}
static void Main(string[] args)
{
Server server = new Server(@"joydip-pc");
Database db = server.Databases["siemensdb"];
List <SqlSmoObject> list = new List <SqlSmoObject>();
DataTable dataTable = db.EnumObjects(DatabaseObjectTypes.StoredProcedure);
foreach (DataRow row in dataTable.Rows)
{
string sSchema = (string)row["Schema"];
if (sSchema == "sys" || sSchema == "INFORMATION_SCHEMA")
{
continue;
}
StoredProcedure sp = (StoredProcedure)server.GetSmoObject(
new Urn((string)row["Urn"]));
if (!sp.IsSystemObject)
{
list.Add(sp);
}
}
Scripter scripter = new Scripter();
scripter.Server = server;
scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.ToFileOnly = true;
scripter.Options.FileName = @"E:\Siemens\C2C-2019\CSharp\Siemens-C2C2019-CSharp\Siemens-C2C2019-CSharp\Day-11\StoredProcduresFromDatabase\StoredProcedures.sql";
scripter.Script(list.ToArray());
}
/// <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);
}
}
/// <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);
}
}
static void Main(string[] args)
{
Server server = new Server(@".\SQLEXPRESS");
Database db = server.Databases["Northwind"];
List <SqlSmoObject> list = new List <SqlSmoObject>();
DataTable dataTable = db.EnumObjects(DatabaseObjectTypes.StoredProcedure);
foreach (DataRow row in dataTable.Rows)
{
string sSchema = (string)row["Schema"];
if (sSchema == "sys" || sSchema == "INFORMATION_SCHEMA")
{
continue;
}
StoredProcedure sp = (StoredProcedure)server.GetSmoObject(
new Urn((string)row["Urn"]));
if (!sp.IsSystemObject)
{
list.Add(sp);
}
}
Scripter scripter = new Scripter();
scripter.Server = server;
scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.ToFileOnly = true;
scripter.Options.FileName = @"C:\StoredProcedures.sql";
scripter.Script(list.ToArray());
}
private static void Method1(SmoServerInfo ServerConnectionData)
{
// Connect to source SQL Server.
Server sourceServer = new Server(new ServerConnection(new SqlConnection(ServerConnectionData.SourceServerConnection)));
// Reference the database.
Database db = sourceServer.Databases[ServerConnectionData.SourceDatabase];
//Database db = sourceServer.Databases[dbName];
// Define a Scripter object and set the required scripting options.
Scripter scripter = new Scripter(sourceServer);
scripter.Options.ScriptDrops = false;
scripter.Options.WithDependencies = true;
scripter.Options.Indexes = true; // To include indexes
scripter.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 = scripter.Script(new Urn[] { tb.Urn });
foreach (string st in sc)
{
//ado.net to destination
Console.WriteLine(st);//SqlCommand.ExecuteNonQuery();
}
Console.WriteLine("--");
}
}
}
/// <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 void GenerateSchemaBasedScriptFiles(string connectionString, Scripter scripter, string destinationDirectory, List <Urn> urns)
{
var serverConnection = CreateServerConnection(connectionString);
var server = new Server(serverConnection);
try
{
server.ConnectionContext.Connect();
var sequenceNo = 1;
foreach (var urn in urns)
{
if (processedUrns.Contains(urn))
{
continue;
}
var smo = server.GetSmoObject(urn) as ScriptNameObjectBase;
if (null != smo)
{
var baseFileName = $"{smo.Name}";
if (smo is ScriptSchemaObjectBase)
{
var ssmo = smo as ScriptSchemaObjectBase;
if (!string.IsNullOrEmpty(ssmo.Schema))
{
baseFileName = $"{ssmo.Schema}.{ssmo.Name}";
}
}
scripter.Options.FileName = Path.Combine(destinationDirectory, $"{sequenceNo.ToString("000")}-{baseFileName}.sql");
scripter.Script(new Urn[] { urn });
processedUrns.Add(urn);
sequenceNo++;
TraceService.Info($"Generated script file {scripter.Options.FileName}");
}
else
{
TraceService.Error($"Failed to generate scripts for urn: {urn}");
}
}
}
catch (Exception ex)
{
TraceService.Error($"Error generating schema files. {ex.ToString()}");
throw;
}
finally
{
if (server.ConnectionContext.IsOpen)
{
server.ConnectionContext.Disconnect();
server = null;
}
}
}
/// <summary>
/// Smoes the generate SQL.
/// </summary>
/// <param name="serverName">Name of the curren server.</param>
/// <param name="dbName">Name of the database.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="tableSchema">The table schema.</param>
/// <returns></returns>
private static StringBuilder SmoGenerateSql(string serverName, string username, string password, string dbName, string tableName, string tableSchema)
{
var serverCon = new ServerConnection(serverName)
{
LoginSecure = false,
Login = username,
Password = password
};
var server = new Server(serverCon);
Database db = server.Databases[dbName];
var list = new List <Urn> {
db.Tables[tableName, tableSchema].Urn
};
foreach (Index index in db.Tables[tableName, tableSchema].Indexes)
{
list.Add(index.Urn);
}
foreach (ForeignKey foreignKey in db.Tables[tableName, tableSchema].ForeignKeys)
{
list.Add(foreignKey.Urn);
}
foreach (Trigger triggers in db.Tables[tableName, tableSchema].Triggers)
{
list.Add(triggers.Urn);
}
Scripter scripter = new Scripter
{
Server = server,
Options =
{
IncludeHeaders = true,
SchemaQualify = true,
SchemaQualifyForeignKeysReferences = true,
NoCollation = true,
DriAllConstraints = true,
DriAll = true,
DriAllKeys = true,
DriIndexes = true,
ClusteredIndexes = true,
NonClusteredIndexes = true,
ToFileOnly = false
}
};
StringCollection scriptedSql = scripter.Script(list.ToArray());
var sb = new StringBuilder();
foreach (string s in scriptedSql)
{
sb.AppendLine(s);
}
return(sb);
}
public static void tralala(string connexion1, List <string> include)
{
//Obtenion de la DataBase de la premiere Connection---------------------------------------------------------------
ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings[connexion1];
SqlConnection Connection = new SqlConnection(connectionString.ToString());
//SMO Server object setup with SQLConnection.
Server server = new Server(new ServerConnection(Connection));
string[] opt = connectionString.ToString().Split(';');
string dbName = "";
for (int i = 0; i < opt.Length; i++)
{
if (opt[i].Contains("Initial Catalog"))
{
dbName += opt[i].Split('=')[1];
}
}
//Set Database to the database
Database db = server.Databases[dbName];
//----------------------------------------------------------------------------------------------------------
// Define a Scripter object and set the required scripting options.
Scripter scrp = new Scripter(server);
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.SchemaQualify = true;
// Iterate through the tables in database and script each one. Display the script.
foreach (Schema tb in db.Schemas)
{
// check if the table is not a system table
if (tb.IsSystemObject == false)
{
if (include.Contains(tb.Name))
{
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 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;
}
private void ScriptDatabaseUsers(Database db, Scripter scr)
{
if (!IncludeUsers)
{
return;
}
List <User> lstUsers = GetSQLUsers(db);
scr.Script(lstUsers.ToArray());
}
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();
}
}
}
}
}
void ScriptIt(Urn[] urn, Scripter scrp, string filename)
{
scrp.Options.FileName = filename;
//override previous settings.
scrp.Options.IncludeIfNotExists = true;
//var createPattern = new Regex(@"(N'CREATE)(.*)$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
var createPattern = new Regex(@"CREATE PROCEDURE \[(.*)\]\.\[(.*)\](.*)", RegexOptions.Multiline | RegexOptions.IgnoreCase);
//createPattern = new Regex(@"CREATE PROCEDURE (dbo\..*)(\(.*)");
try
{
scrp.Script(urn);
var reader = new StreamReader(filename);
var createScript = reader.ReadToEnd();
reader.Close();
//Did the initial research on this. For our needs it's not that helpful. To many regular expressions required.
/*
var alterScript = createPattern.Replace(createScript, @"CREATE PROCEDURE [$1].[$2] AS RAISERROR(''Empty Stored Proc'', 16, 1) WITH SETERROR'
END
GO
ALTER PROCEDURE [$1].[$2]
$3");
int index = alterScript.LastIndexOf("'");
alterScript = alterScript.Substring(0, alterScript.LastIndexOf("'")) + "GO";
var writer = new StreamWriter(filename);
writer.Write(alterScript);
writer.Close();
*/
}
catch (Exception e)
{
Console.WriteLine("Scripting for this object FAILED for the following reason:");
Console.WriteLine(e.InnerException);
Console.WriteLine("");
return;
}
objectCount++;
}
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;
}
void ScriptIt(Urn[] urn, Scripter scrp, string filename)
{
scrp.Options.FileName = filename;
try
{
scrp.Script(urn);
}
catch (Exception e)
{
Console.WriteLine("Scripting for this object FAILED for the following reason:");
Console.WriteLine(e.InnerException);
Console.WriteLine("");
return;
}
objectCount++;
}
private void DoProcess()
{
try
{
Scripter s = new Scripter();
s.ServerName = txtServer.Text;
s.DatabaseName = txtDb.Text;
s.UserId = txtUser.Text;
s.Password = txtPass.Text;
s.OutputPath = txtOutput.Text;
s.ScriptKeys = cbScriptKeys.Checked;
s.ScriptPerms = cbScriptPerms.Checked;
s.ScriptDrops = cbScriptDrops.Checked;
s.SeparateOutput = cbSeparateOutput.Checked;
Output("");
s.Script(txtObjects.Lines);
if (cbLaunchDir.Checked)
Launch();
Output("Finished!");
}
catch (Exception exp)
{
MessageBox.Show(exp.ToString());
}
}
public void Script(string srvname, string dbName, string destination)
{
tbxOutput.Text = "Scripting the " + dbName + " database." + "\r\n";
if (destination == "")
{
destination = Environment.CurrentDirectory + "\\";
}
else
{
if (destination[destination.Length - 1] != Convert.ToChar("\\"))
{
destination += "\\";
}
}
tbxOutput.Text += "Output directory set to " + destination + "\r\n";
/* *************************** */
/* CHECK FOR VALID DESTINATION */
/* *************************** */
tbxOutput.Text += "Checking for valid destination directory...\r\n";
if (!Directory.Exists(destination))
{
throw new DirectoryNotFoundException("The specified destination directory does not exist.");
}
else
{
tbxOutput.Text += "Destination directory is valid.\r\n";
/* *********************** */
/* CREATE FOLDER STRUCTURE */
/* *********************** */
tbxOutput.Text += "Establishing folder structure...\r\n";
newFolders.Clear();
try
{
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd")))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + @"\Programmability\Functions\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\" + "\r\n";
}
}
catch
{
throw new UnauthorizedAccessException("The program failed to create the backup folders in the specified directory. Please check security settings.");
}
tbxOutput.Text += "Folder structure established \r\n";
}
/* *************** */
/* Generate Script */
/* *************** */
try //Wrap in try statement to catch incorrect server errors
{
tbxOutput.Text += "Connecting to server " + srvname + "...\r\n";
Server srv;
srv = new Server(srvname);
srv.ConnectionContext.LoginSecure = true;
if (!srv.Databases.Contains(dbName))
{
RemoveFolders();//Clean out folders creating during this run
throw new ArgumentException("The specified database could not be found.");
}
Database db = new Database();
db = srv.Databases[dbName];
Scripter scr = new Scripter(srv);
Scripter scrFullScript = new Scripter(srv);
srv.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
/* Create Options for the scr Scripter */
ScriptingOptions options = new ScriptingOptions();
options.IncludeHeaders = true;
options.AppendToFile = false;
options.ToFileOnly = true;
options.DriAll = true;
options.IncludeDatabaseContext = true;
//options.ScriptDrops = true;
scr.Options = options; //Assign options to scr
/* Create options for the scrFullScript Scripter */
ScriptingOptions scopFull = new ScriptingOptions();
scopFull.IncludeHeaders = true;
scopFull.AppendToFile = true;
scopFull.ToFileOnly = true;
scopFull.DriAll = true;
scopFull.IncludeDatabaseContext = true;
scopFull.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + "_FULL.sql";
scrFullScript.Options = scopFull; //Assign options to scrFullScript
/* ******************* */
/* CREATE SCRIPT FILES */
/* ******************* */
List<string> lstErrors = new List<string>();
//SCRIPT DATABASE
Microsoft.SqlServer.Management.Smo.Database[] dbs = new Microsoft.SqlServer.Management.Smo.Database[1];
tbxOutput.Text += "Scripting Database: " + db + "\r\n";
dbs[0] = db;
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + ".sql";
scr.Script(dbs);
scrFullScript.Script(dbs);
tbxOutput.Text += "Scripting Database Complete.\r\n";
//SCRIPT TABLES
Microsoft.SqlServer.Management.Smo.Table[] tbl = new Microsoft.SqlServer.Management.Smo.Table[1];
tbxOutput.Text += "Scripting Tables...\r\n";
for (int idx = 0; idx < db.Tables.Count; idx++)
{
if (!db.Tables[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Table: " + db.Tables[idx] + "\r\n";
tbl[0] = db.Tables[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"
+ tbl[0].Name + ".sql";
scr.Script(tbl);
scrFullScript.Script(tbl);
}
}
tbxOutput.Text += "Scripting Tables Complete.\r\n";
//SCRIPT VIEWS
Microsoft.SqlServer.Management.Smo.View[] vw = new Microsoft.SqlServer.Management.Smo.View[1];
tbxOutput.Text += "Scripting Views...\r\n";
for (int idx = 0; idx < db.Views.Count; idx++)
{
if (!db.Views[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting View: " + db.Views[idx] + "\r\n";
vw[0] = db.Views[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"
+ vw[0].Name + ".sql";
scr.Script(vw);
scrFullScript.Script(vw);
}
}
tbxOutput.Text += "Scripting Views Complete.\r\n";
//SCRIPT STORED PROCEDURES
Microsoft.SqlServer.Management.Smo.StoredProcedure[] proc = new Microsoft.SqlServer.Management.Smo.StoredProcedure[1];
tbxOutput.Text += "Scripting Stored Procedures...\r\n";
for (int idx = 0; idx < db.StoredProcedures.Count; idx++)
{
if (!db.StoredProcedures[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Stored Procedure: " + db.StoredProcedures[idx] + "\r\n";
proc[0] = db.StoredProcedures[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"
+ proc[0].Name + ".sql";
scr.Script(proc);
scrFullScript.Script(proc);
}
}
tbxOutput.Text += "Scripting Stored Procedures Complete.\r\n";
//SCRIPT FUNCTIONS
Microsoft.SqlServer.Management.Smo.UserDefinedFunction[] udf = new Microsoft.SqlServer.Management.Smo.UserDefinedFunction[1];
tbxOutput.Text += "Scripting User Defined Functions...\r\n";
for (int idx = 0; idx < db.UserDefinedFunctions.Count; idx++)
{
if (!db.UserDefinedFunctions[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting User Defined Function: " + db.UserDefinedFunctions[idx] + "\r\n";
udf[0] = db.UserDefinedFunctions[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\"
+ udf[0].Name + ".sql";
scr.Script(udf);
scrFullScript.Script(udf);
}
}
tbxOutput.Text += "Scripting User Defined Functions complete.\r\n";
tbxOutput.Text += "Scripting master file...\r\n";
try
{
String strFullScript = "";
String strFullOutput = "";
tbxOutput.Text += "Retrieving full script...\r\n";
using (StreamReader sr = new StreamReader(scopFull.FileName))
{
strFullScript = sr.ReadToEnd();
}
tbxOutput.Text += "Full script retrieved.\r\n";
//strFullOutput = strFullScript;//Temporary
string[] arrFullScript = Regex.Split(strFullScript, "GO");
foreach (string line in arrFullScript)
{
if(!line.StartsWith("\r\nALTER TABLE"))
strFullOutput += line + "GO\r\n";
}
foreach (string line in arrFullScript)
{
if (line.StartsWith("\r\nALTER TABLE"))
strFullOutput += line + "GO\r\n";
}
string strConditionalDrop = "\r\n\r\nIF DB_ID('" + dbName + "') IS NOT NULL\r\nBEGIN\r\n"
+ " ALTER DATABASE " + dbName + "\r\n"
+ " SET SINGLE_USER\r\n"
+ " WITH ROLLBACK IMMEDIATE;\r\n"
+ " DROP DATABASE " + dbName + ";\r\n"
+ "END\r\n";
strFullOutput = strFullOutput.Insert(strFullOutput.IndexOf("GO") + 2, strConditionalDrop);
tbxOutput.Text += "Writing corrected full script...\r\n";
using (StreamWriter sw = new StreamWriter(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\" + dbName + "_FULL.sql"))
{
sw.Write(strFullOutput);
}
tbxOutput.Text += "Full script successfully written.\r\n";
tbxOutput.Text += "Scripting master file complete.\r\n";
}
catch
{
tbxOutput.Text += "ERROR Scripting Master File Failed.\r\n";
lstErrors.Add("Scripting Master File Failed.");
}
tbxOutput.Text += "=================================\r\n";
if (lstErrors.Count == 0)
tbxOutput.Text += "SCRIPTING COMPLETED SUCCESSFULLY.\r\n";
else
{
tbxOutput.Text += "SCRIPTING COMPLETED WITH ERRORS.\r\n";
tbxOutput.Text += String.Format("The following {0} errors occurred:\r\n", lstErrors.Count);
foreach (string error in lstErrors)
{
tbxOutput.Text += error + "\r\n";
}
}
ActiveControl = btnClose;
}
catch (ConnectionFailureException) //Error type thrown by attempt to bind invalid server name
{
//throw new ConnectionFailureException("A connection to the specified server could not be made. Please check the supplied server name and try again.");
tbxOutput.Text += "Connection to server failed.\r\n";
RemoveFolders();//Clean out folders creating during this run
tbxOutput.Text += "A connection to the specified server could not be made. Please check the supplied server name and try again.\r\n";
}
catch //General Catch-All re-throws error without further handling
{
RemoveFolders();//Clean out folders creating during this run
throw;
}
}
/// <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["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);
}
}
private void BuildScript(Scripter scripter, List<Urn> urns, string fileName)
{
var sc = scripter.Script(urns.ToArray());
using (var writer = File.CreateText(ScriptPath + fileName))
{
foreach (var st in sc)
{
writer.WriteLine(st.Trim('\r', '\n') + "\r\nGO\r\n");
}
}
}
public void Script()
{
Server s = new Server(serverName);
Scripter scripter = new Scripter(s);
List<SqlSmoObject> objects = new List<SqlSmoObject>();
Database db = new Database();
foreach (Database d in s.Databases)
{
if (d.Name == dbName)
{
db = d;
}
}
int ignoreCount = 0;
foreach (Table t in db.Tables)
{
if (!t.IsSystemObject)
{
objects.Add(t);
}
else
{
ignoreCount++;
}
}
foreach (StoredProcedure sp in db.StoredProcedures)
{
if (!sp.IsSystemObject)
{
objects.Add(sp);
}
else
{
ignoreCount++;
}
}
foreach (UserDefinedFunction f in db.UserDefinedFunctions)
{
if (!f.IsSystemObject)
{
objects.Add(f);
}
else
{
ignoreCount++;
}
}
Console.WriteLine("{0} objects to script, {1} ignored", objects.Count, ignoreCount);
scripter.Options.Add(ScriptOption.WithDependencies);
Console.WriteLine(scripter.Options);
StringCollection commands = scripter.Script(objects.ToArray());
foreach (string cmd in commands)
{
Console.WriteLine(cmd);
}
}
public static IEnumerable<string> DropSchemas(this Database db)
{
var scr = new Scripter(GenServer);
var resultScript = new List<string> { Comment("Drop schemas") };
scr.Options.ScriptDrops = true;
scr.Options.IncludeIfNotExists = true;
foreach (var schema in db.Schemas.Cast<Schema>().Where(x => x.IsSystemObject == false))
{
resultScript.AddRange(scr.Script(new[] { schema.Urn }).Cast<string>());
}
return resultScript;
}
public static IEnumerable<string> CreateProcedures(this Database db)
{
var scr = new Scripter(GenServer);
var resultScript = new List<string> { Comment("Create procedures") };
scr.Options.IncludeIfNotExists = true;
scr.Options.ScriptSchema = true;
foreach (var procedure in db.StoredProcedures.Cast<StoredProcedure>().Where(x => x.IsSystemObject == false))
{
resultScript.AddRange(scr.Script(new[] { procedure.Urn }).Cast<string>());
}
return resultScript;
}
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();
}
//public static IEnumerable<string> DropProceduresWithoutDatabaseContext(this Database db)
//{
// var scr = new Scripter(GenServer);
// var resultScript = new List<string> {Comment("Drop procedures")};
// scr.Options.IncludeDatabaseContext = false;
// scr.Options.ScriptDrops = true;
// scr.Options.IncludeIfNotExists = true;
// scr.Options.WithDependencies = true;
// foreach (var procedure in db.StoredProcedures.Cast<StoredProcedure>().Where(x => x.IsSystemObject == false))
// {
// resultScript.AddRange(scr.Script(new[] { procedure.Urn }).Cast<string>());
// }
// return resultScript;
//}
public static IEnumerable<string> DropViews(this Database db)
{
var scr = new Scripter(GenServer);
var resultScript = new List<string> {Comment("Drop views")};
scr.Options.ScriptDrops = true;
scr.Options.IncludeIfNotExists = true;
scr.Options.WithDependencies = true;
foreach (var view in db.Views.Cast<View>().Where(x => x.IsSystemObject == false))
{
resultScript.AddRange(scr.Script(new[] { view.Urn }).Cast<string>());
}
return resultScript;
}
private void ScriptTableButton_Click(System.Object sender,
System.EventArgs e)
{
Cursor csr = null;
Database db;
StringCollection strColl;
Scripter scrptr;
SqlSmoObject[] smoObjects;
Table tbl;
Int32 count;
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Use the selected database
db = (Database)DatabasesComboBox.SelectedItem;
if (db.Name.Length == 0)
{
ExceptionMessageBox emb = new ExceptionMessageBox();
emb.Text = Properties.Resources.NoDatabaseSelected;
emb.Show(this);
return;
}
// Create scripter object
scrptr = new Scripter(SqlServerSelection);
if (ScriptDropCheckBox.CheckState == CheckState.Checked)
{
scrptr.Options.ScriptDrops = true;
}
else
{
scrptr.Options.ScriptDrops = false;
}
scrptr.DiscoveryProgress +=
new ProgressReportEventHandler(
this.ScriptTable_DiscoveryProgressReport);
scrptr.ScriptingProgress +=
new ProgressReportEventHandler(
this.ScriptTable_ScriptingProgressReport);
if (TablesComboBox.SelectedIndex >= 0)
{
// Get selected table
smoObjects = new SqlSmoObject[1];
tbl = (Table)TablesComboBox.SelectedItem;
if (tbl.IsSystemObject == false)
{
smoObjects[0] = tbl;
}
if (DependenciesCheckBox.CheckState == CheckState.Checked)
{
scrptr.Options.WithDependencies = true;
}
else
{
scrptr.Options.WithDependencies = false;
}
strColl = scrptr.Script(smoObjects);
// Clear control
ScriptTextBox.Clear();
count = 0;
foreach (String str in strColl)
{
count++;
sbrStatus.Text = string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.AppendingScript, count,
strColl.Count);
ScriptTextBox.AppendText(str);
ScriptTextBox.AppendText(Properties.Resources.Go);
}
}
else
{
ExceptionMessageBox emb = new ExceptionMessageBox();
emb.Text = Properties.Resources.ChooseTable;
emb.Show(this);
}
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
// Clean up
sbrStatus.Text = Properties.Resources.Done;
// Restore the original cursor
this.Cursor = csr;
}
}
public static IEnumerable<string> DropTables(this Database db)
{
var scr = new Scripter(GenServer);
var resultScript = new List<string> { Comment("Drop tables") };
scr.Options.ScriptDrops = true;
scr.Options.IncludeIfNotExists = true;
scr.Options.WithDependencies = true; //automatically adding dependencies for views, procedures, etc.
foreach (var table in db.Tables.Cast<Table>().Where(x => x.IsSystemObject == false))
{
resultScript.AddRange(scr.Script(new[] { table.Urn }).Cast<string>());
}
return resultScript;
}