public SQLConnection(SQLObjectName myConnectionObject)
{
try
{
connServer = new Server(myConnectionObject.ServerName);
connDatabase = connServer.Databases[myConnectionObject.DatabaseName];
scripter = new Scripter(connServer);
// Set Scripter to SQL 2012 target. (Doesn't help much since it still generates SQL 2014+ options)
scripter.Options.TargetServerVersion = Microsoft.SqlServer.Management.Smo.SqlServerVersion.Version110;
Console.WriteLine("Connected to: " + connServer.Name + " - " + connDatabase.Name);
}
catch (Exception e)
{
Console.WriteLine("*** Error: Unable to connect to database.");
Console.WriteLine(e.InnerException.Message);
Console.WriteLine(e.Message);
}
}
private static void GetDBScripts(string DBTablesListFilename)
{
Console.WriteLine("------- Extracting scripts from database/s and/or files -------");
// Get the list of SQL script files we want to combine into this model
try
{
string contents = File.ReadAllText(DBTablesListFilename);
// Split the list of files into an array.
string[] scriptobjects = contents.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
// Setup a new SQLObjectName object.
SQLObjectName oname = new SQLObjectName(String.Empty);
string outputScript = string.Empty;
SQLConnection sconn = new SQLConnection();
// Check through each line for DB instruction, or object name.
foreach (string scriptobject in scriptobjects)
{
// Check for commented out lines
if ((scriptobject.StartsWith(@"--") || scriptobject.StartsWith(@"//")) == false)
{
oname = new SQLObjectName(scriptobject);
if (oname.ObjectType != SQLObjectType.ParseError)
{
if (oname.ObjectType == SQLObjectType.Database)
{
// Set up new database connection object.
sconn = new SQLConnection(oname);
}
else if (oname.ObjectType == SQLObjectType.ScriptFile)
{
// Extract script from File
GetScriptFile(oname.ScriptFile);
}
else
{
// Extract script from SQL Server
if (sconn.connDatabase != null)
{
sconn.ExtractScript(oname, mySQLScripts, PrintVerboseInfo);
}
else
{
Console.WriteLine("Warning - Could not retrieve: (" + oname.ObjectType.ToString() + ") - " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
mySQLScripts.MissingScripts.Add("No db connection for object: (" + oname.ObjectType.ToString() + ") - " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
}
}
}
else
{
mySQLScripts.MissingScripts.Add("Unable to parse SQL object from string: " + oname.Name);
}
}
}
}
catch (System.IO.FileNotFoundException e)
{
Console.WriteLine("**** File not found error: " + DBTablesListFilename);
Console.WriteLine("**** Error: " + e.Message);
}
catch (Exception e)
{
Console.WriteLine("**** Error: " + e.Message);
}
}
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);
}
}
