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

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:

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

2 Answers

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)

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.