public static void Test()
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
using (System.Data.SqlClient.SqlConnection con = (System.Data.SqlClient.SqlConnection)
//SqlFactory.GetConnection()
SqlFactory.LocalConntection
)
{
Microsoft.SqlServer.Management.Common.ServerConnection sc =
new Microsoft.SqlServer.Management.Common.ServerConnection(con);
lock (con)
{
sc.Connect();
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(sc);
Microsoft.SqlServer.Management.Smo.Database database; // = new Microsoft.SqlServer.Management.Smo.Database();
//database = server.Databases["redmine"];
//string schemaName = @"dbo";
//string tableName = @"issues";
database = server.Databases["COR_Basic_Demo_V4"];
string schemaName = @"dbo";
string tableName = @"T_Benutzer";
Microsoft.SqlServer.Management.Smo.Table table = database.Tables[tableName, schemaName];
System.Collections.Specialized.StringCollection result = table.Script();
// table.Script(new ScriptingOptions() { ScriptForAlter = true });
Microsoft.SqlServer.Management.Smo.StoredProcedure proc = database.StoredProcedures["procname", "schema"];
// proc.Script(new ScriptingOptions() { ScriptForAlter = true });
// string alterText = proc.ScriptHeader(true) + proc.TextBody;
foreach (string line in result)
{
sb.AppendLine(line);
} // Next line
sc.Disconnect();
} // End Lock con
} // End Using con
using (System.IO.FileStream fs = System.IO.File.OpenWrite(@"d:\testScriptSAQl.sql"))
{
using (System.IO.TextWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.UTF8))
{
sw.Write(sb.ToString());
sw.Flush();
fs.Flush();
} // End Using sw
} // End Using fs
} // End Sub
/// <summary>
/// Compare 2 schema Tables
/// </summary>
/// <param name="sp1">your first smo Table</param>
/// <param name="sp2">your second smo Table</param>
/// <param name="checkComments">True if you want to compare comments</param>
/// <param name="checkBrackets">True if you want to compare scripts with brackets</param>
/// <param name="ignoreCaseSensitive">True if you want to ignore Case Sensitive. False if Case sensitive</param>
/// <returns></returns>
public static KMOCompareInfo CompareSchema(this smo.Table t1, smo.Table t2, bool checkComments = false, bool checkBrackets = false, bool ignoreCaseSensitive = true)
{
smo.ScriptingOptions so = new smo.ScriptingOptions();
so.DriAll = true;
string s1 = String.Join(Environment.NewLine, t1.Script(so).Cast <String>().Select(s => s.ToString()).AsEnumerable());
string s2 = String.Join(Environment.NewLine, t2.Script(so).Cast <String>().Select(s => s.ToString()).AsEnumerable());
string message = string.Empty;
if (t1.Columns.Count != t2.Columns.Count)
{
message += "Tables don't have the same column count. ";
}
foreach (smo.Column c1 in t1.Columns)
{
smo.Column c2 = t2.Columns[c1.Name];
if (c2 == null)
{
message += "The column " + c1.Name + " doesn't exist in the second table. ";
}
else if (c2.DataType.Name != c1.DataType.Name || c2.DataType.NumericPrecision != c1.DataType.NumericPrecision || c2.DataType.NumericScale != c1.DataType.NumericScale)
{
message += "The column " + c1.Name + " doesn't have the same type. ";
}
}
foreach (smo.Column c2 in t2.Columns)
{
smo.Column c1 = t1.Columns[c2.Name];
if (c1 == null)
{
message += "The column " + c2.Name + " doesn't exist in the first table. ";
}
}
if (message != string.Empty)
{
return(new KMOCompareInfo {
IsIdentical = false, Message = message, Script1 = s1, Script2 = s2
});
}
if (KMOCompareHelper.CompareScript(s1, s2, ignoreCaseSensitive, checkComments, checkBrackets))
{
return(new KMOCompareInfo {
IsIdentical = true, Message = string.Empty, Script1 = s1, Script2 = s2
});
}
else
{
return(new KMOCompareInfo {
IsIdentical = false, Message = "Script difference", Script1 = s1, Script2 = s2
});
}
}
private void GenerateScript()
{
this._scriptBody = String.Empty;
try
{
SMO.Database smoDb = _context.SmoServer.Databases[this._dbName];
if (smoDb == null)
{
this._scriptBody = String.Format(
"Database '{0}' not found. No T-SQL script generated.",
this._dbName);
Log.Warning(this._scriptBody);
return;
}
SMO.Table smoTable = smoDb.Tables[this._tableName, this._schemaName];
if (smoTable == null)
{
this._scriptBody = String.Format(
"Table '{0}' not found in Schema '{1}' in Database {2}. No T-SQL script generated.",
this._tableName, this._schemaName, this._dbName);
Log.Warning(this._scriptBody);
return;
}
StringCollection scripts = smoTable.Script();
foreach (var script in scripts)
{
this._scriptBody += script;
}
}
catch (Exception e)
{
this._scriptBody = String.Format(
"Error generating script for Table '{0}' in Schema '{1}' in Database '{2}'\n\n{3}",
this._tableName, this._schemaName, this._dbName, e.ToString());
Log.Error(this._scriptBody);
}
}
public string ScriptEverything(Database selectedDB, ObjectsToScriptOptions objectsToScript)
{
Microsoft.SqlServer.Management.Smo.Table tbl = null;
StringBuilder sb = new StringBuilder();
objectsToScriptOptions = objectsToScript;
SelectedDB = selectedDB;
ClearVariables();
Console.WriteLine("Scripting tables started at : " + DateTime.Now);
Table[] tbls = new Table[SelectedDB.Tables.Count];
SelectedDB.Tables.CopyTo(tbls, 0);
DependencyTree tree = DBServerConnectionSingleton.Instance.DBScripter.DiscoverDependencies(tbls, true);
DependencyWalker depwalker = new Microsoft.SqlServer.Management.Smo.DependencyWalker();
DependencyCollection depcoll = depwalker.WalkDependencies(tree);
ScriptSchemas();
SQLScript += SQLScriptSchemas;
sb.AppendLine("--================ Start of Table objects script ( " + DateTime.Now + " ) ================");
sb.AppendLine(Environment.NewLine);
foreach (DependencyCollectionNode dep in depcoll)
{
tbl = SelectedDB.Tables[dep.Urn.GetAttribute("Name"), dep.Urn.GetAttribute("Schema")];
ScriptingOptions options = new ScriptingOptions();
options.NoCollation = true;
options.ClusteredIndexes = false;
options.Default = true;
options.DriAll = false;
options.Indexes = false;
options.IncludeHeaders = true;
options.IncludeIfNotExists = true;
options.SchemaQualify = true;
options.DriDefaults = true;
if (!tbl.IsSystemObject)
{
if (objectsToScript.ScriptTables)
{
StringCollection coll = tbl.Script(options);
foreach (string str in coll)
{
sb.Append(str);
sb.Append(Environment.NewLine);
}
sb.AppendLine("GO");
}
Console.WriteLine(tbl.Name);
if (objectsToScript.ScriptIndexes)
{
ScriptIndexes(tbl);
}
if (objectsToScript.ScriptForeignKeys)
{
ScriptForeignKeys(tbl);
}
if (objectsToScript.ScriptTriggers)
{
ScriptTriggers(tbl);
}
}
}
sb.AppendLine("--================ End of Table objects script ( " + DateTime.Now + " ) ================");
sb.AppendLine(Environment.NewLine);
Console.WriteLine("Scripting tables finished at : " + DateTime.Now);
if (objectsToScript.ScriptUserDefinedFunctions)
{
ScriptUserDefinedFunctions(selectedDB);
}
if (objectsToScript.ScriptStoredProcs)
{
ScriptStoredProcs(selectedDB);
}
if (objectsToScript.ScriptViews)
{
ScriptViews(selectedDB);
}
SQLScript += sb.ToString();
SQLScript += SQLScriptFunctions;
return(SQLScript);
}
public void ExtractScript(SQLObjectName oname, SQLScripts SQLScriptsCollection, bool Verbose)
{
// Store extracted scripts. Each extract may include multiple scripts.
StringCollection OutputScripts = new StringCollection();
string FinalScript = String.Empty;
switch (oname.ObjectType)
{
case SQLObjectType.Table:
Microsoft.SqlServer.Management.Smo.Table scriptTable = connDatabase.Tables[oname.Name, oname.Schema];
if (scriptTable != null)
{
StringCollection CheckScripts = new StringCollection(); // Store scripts to be checked
String TableScript = String.Empty; // Stores individual script for output collection.
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.DriAll = true;
scriptOptions.Statistics = true;
scriptOptions.ClusteredIndexes = true;
scriptOptions.NonClusteredIndexes = true;
scriptOptions.DriAllConstraints = true;
scriptOptions.WithDependencies = false;
// Get table and related scripts
CheckScripts = scriptTable.Script(scriptOptions);
// Check scripts so we can remove invalide SQL 2012 column store options from the script.
// (Why doesn't the target server version remove this?
// This is a crappy place to do this, and it's version specific.
// Need to implement the new versioning code to check target model.
foreach (string CheckCCI in CheckScripts)
{
if (CheckCCI.Contains(", DATA_COMPRESSION = COLUMNSTORE"))
{
TableScript = CheckCCI.Replace(", DATA_COMPRESSION = COLUMNSTORE", "");
}
else
{
TableScript = CheckCCI;
}
// Add the script into the OutputScripts collection.
OutputScripts.Add(TableScript);
}
}
break;
case SQLObjectType.View:
Microsoft.SqlServer.Management.Smo.View scriptView = connDatabase.Views[oname.Name, oname.Schema];
if (scriptView != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.DriAll = true;
scriptOptions.ClusteredIndexes = true;
scriptOptions.NonClusteredIndexes = true;
scriptOptions.WithDependencies = false;
// Must specify tables seperatly, but safer to do so
// to avoid having duplicate table names in the model.
OutputScripts = scriptView.Script(scriptOptions);
}
break;
case SQLObjectType.StoredProcedure:
Microsoft.SqlServer.Management.Smo.StoredProcedure scriptStoredProcedure = connDatabase.StoredProcedures[oname.Name, oname.Schema];
if (scriptStoredProcedure != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptStoredProcedure.Script(scriptOptions);
}
break;
case SQLObjectType.PartitionScheme:
{
Microsoft.SqlServer.Management.Smo.PartitionScheme scriptPScheme = connDatabase.PartitionSchemes[oname.Name];
if (scriptPScheme != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptPScheme.Script(scriptOptions);
}
}
break;
case SQLObjectType.PartitionFunction:
{
Microsoft.SqlServer.Management.Smo.PartitionFunction scriptPFunction = connDatabase.PartitionFunctions[oname.Name];
if (scriptPFunction != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptPFunction.Script(scriptOptions);
}
}
break;
case SQLObjectType.Schema:
{
Microsoft.SqlServer.Management.Smo.Schema scriptSchema = connDatabase.Schemas[oname.Name];
if (scriptSchema != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
scriptOptions.ScriptOwner = true; // This includes the "with authorize" part.
OutputScripts = scriptSchema.Script(scriptOptions);
}
}
break;
case SQLObjectType.FileGroup:
{
Microsoft.SqlServer.Management.Smo.FileGroup scriptFG = connDatabase.FileGroups[oname.Name];
if (scriptFG != null)
{
// Create manual script for FileGroups
OutputScripts.Add("ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP " + scriptFG.Name);
}
}
break;
case SQLObjectType.User:
{
Microsoft.SqlServer.Management.Smo.User scriptUser = connDatabase.Users[oname.Name];
if (scriptUser != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptUser.Script(scriptOptions);
}
}
break;
case SQLObjectType.Function:
Microsoft.SqlServer.Management.Smo.UserDefinedFunction userDefinedFunction = connDatabase.UserDefinedFunctions[oname.Name, oname.Schema];
if (userDefinedFunction != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = userDefinedFunction.Script(scriptOptions);
}
break;
}
if (OutputScripts.Count > 0)
{
Console.WriteLine("Extracted SQL script: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
foreach (string script in OutputScripts)
{
// Add the script to the script collection.
FinalScript = FinalScript + script + Environment.NewLine + "GO" + Environment.NewLine;
}
}
else
{
Console.WriteLine("Warning - Could not retrieve: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
FinalScript = String.Empty;
}
if (FinalScript != String.Empty)
{
SQLScriptsCollection.Scripts.Add(FinalScript);
}
else
{
SQLScriptsCollection.MissingScripts.Add("Missing SQL object: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
}
// Print script(s) if verbose is on.
if (Verbose)
{
Console.WriteLine(FinalScript);
}
}