What I'm trying to do: Create an SQL script using C# to create an exact copy of an existing table.
My Question: How would you define the options in scriptingOptions to insure that the resulting script would create a 100% exact copy of a table? There are 78 options and it's not clear how to do this. Initially, I thought by calling table.Script() without passing any ScriptingOptions that I would be able to get an exact copy of my table, but this doesn't do the trick (for example, indexes are not coded unless they are specified in the scripting options). It seems that I actually have to manually specify each property in ScriptingOptions to get what I want. Which ones do I set to get my desired result? It can't be that hard to just copy a table exactly.
Scripting Options Available: Here are all of the available options: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions_properties.aspx
My Code:
Server server = new Server("XXX");
Database database = new Database();
database = server.Databases["YYY"];
Table table = database.Tables["ZZZ", @"PPP"];
ScriptingOptions scriptingOptions = new ScriptingOptions();
//Define properties in scriptingOptions
StringCollection result = table.Script(scriptingOptions);
var script = "";
foreach (var line in result) {
script += line;
}
System.IO.StreamWriter fs = System.IO.File.CreateText(@"QQQ");
fs.Write(script);
fs.Close();
You want to set the following.