如何在 SQL Server 中使用 SMO 自动生成脚本?

2021-12-29 00:00:00 automation powershell sql-server-2008 sql-server smo

我想在 SSMS 2008 中自动生成脚本(在 SSMS --> 任务 --> 生成脚本).我读到 SQL Server 2008 不支持数据库发布向导(包括 SQLPUBWIZ SCRIPT)，但这种自动化可以在 SQL Server 2008 中使用 SMO 完成.我不知道 SMO 以及如何使用 SMO 执行此操作，所以您能给我一些建议(资源等)如何开始?

I would like to automate script generation (in SSMS --> Tasks --> Generate Scripts) in SSMS 2008. I have read that SQL Server 2008 does not support Database Publishing Wizard (including SQLPUBWIZ SCRIPT) but this automation can be done using SMO in SQL Server 2008. I don't have a clue about SMO and how to do this using SMO, so could you give me some advice (resources etc.) how to begin?

推荐答案

SMO 脚本编写的关键是 Scripter 类.所有其他工具(如 SSMS)都使用这个类来生成对象创建脚本.MSDN 上有一个示例用法:

The key to SMO scripting is the Scripter class. All other tools (like SSMS) use this class to generated object creation scripts. There is an example usage on MSDN:

{ 
   //Connect to the local, default instance of SQL Server. 
  Server srv = new Server(); 

   //Reference the AdventureWorks2008R2 database.  
  Database db = srv.Databases["AdventureWorks2008R2"]; 

   //Define a Scripter object and set the required scripting options. 
  Scripter scrp = new Scripter(srv); 
   scrp.Options.ScriptDrops = false; 
   scrp.Options.WithDependencies = true; 

   //Iterate through the tables in database and script each one. Display the script. 
   //Note that the StringCollection type needs the System.Collections.Specialized namespace to be included. 
   Microsoft.SqlServer.Management.Sdk.Sfc.Urn[] smoObjects = new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[1] ;
   foreach (Table tb in db.Tables) {   
      smoObjects[0] = tb.Urn; 
      if (tb.IsSystemObject == false) { 
         System.Collections.Specialized.StringCollection sc;
         sc = scrp.Script(smoObjects); 
         foreach ( string st in sc) { 
            Console.WriteLine(st); 
         } 
      } 
   } 
}

相关文章