How to set SMO ScriptingOptions to guarantee exact copy of table?

Question

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();

Skipbomb

Answer 1

You want to set the following.

1. ClusteredIndexes = true
2. Default = true
3. FullTextIndexes = true
4. Indexes = true
5. NonClusteredIndexes = true
6. SchemaQualify = true (if you want to script it into the current schema)
7. ScriptData = true (if you want to copy the data)
8. ScriptDrops = true (this will DROP the table in the target database before creating it again)
9. ScriptSchema = true (if you want to script it into the current schema)
10. Statistics = true
11. Triggers = true
12. WithDependencies = true (if you want to script dependent objects)
13. DriAll = true (scripts referential integrity actions such as not allowing an INSERT into a child table if the parent table key doesn't exist)

Furness

Do you know anything about the "Dri" options? What does "Dri" mean? – Skipbomb

@Skipbomb Yes, my apologies, I missed that one. You do want to set the DriAll property to true. I've updated the answer. – Furness

@Skipbomb Did this work for you? If not let me know and I'll help you work it out. Thanks! – Furness

"Dri" = "declarative referential integrity" – Frumenty

@Skipbomb - was this an acceptable answer for you? If not, what's missing that I need to add? If so, can you please mark it as the accepted answer so we all know it worked for you? – Furness

@Mike, I was hoping to hear from other users that your method is "the" right way to do this, but it seems like things aren't quite as black and white as I had hoped. Thanks for your help and sorry for the delay. I will use your solution. – Skipbomb

Also Permissions = true is usefull – Forwhy

Answer 2

I have used smo, and the mistake is that you have to - consider all database objects because of relationship between. when scripting a table you have to script all security object around, foreign keys, extended properties and so on. - identify the correct order to script objects because of relationship As for me the best way to be sure you have same database object is to use database backup.

Tolerate