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

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)



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.



来源:https://stackoverflow.com/questions/11658143/how-to-set-smo-scriptingoptions-to-guarantee-exact-copy-of-table


码神部落- 版权声明 1、本主题所有言论和图片纯属会员个人意见,与码神部落立场无关。
2、本站所有主题由该帖子作者发表,该帖子作者这爷绝蝂码神部落享有帖子相关版权。
3、码神部落管理员和版主有权不事先通知发贴者而删除本文。
4、其他单位或个人使用、转载或引用本文时必须同时征得该帖子作者这爷绝蝂码神部落的同意。
5、帖子作者须承担一切因本文发表而直接或间接导致的民事或刑事法律责任。
6、本帖部分内容转载自其它媒体,但并不代表本站赞同其观点和对其真实性负责。
7、如本帖侵犯到任何版权问题,请立即告知本站,本站将及时予与删除并致以最深的歉意。

最新回复 (0)
    • 码神部落
      2
        立即登录 立即注册 GitHub登录
返回
发新帖
作者最近主题: