sql-server - 如何在 SQL Server Management Studio 2008 中自动执行 "generate scripts"任务？

Question

我想在 SQL Server Management Studio 2008 中自动生成脚本。

现在我所做的是:

• 右键单击我的数据库、“任务”、“生成脚本...”
• 手动选择我需要的所有导出选项，然后在“选择对象”选项卡上点击全选
• 选择导出文件夹
• 最终点击“完成”按钮

有没有办法自动执行此任务？

编辑:我想生成创建脚本，而不是更改脚本。

Answer 1

与 SSMS 中的脚本生成相比，SqlPubwiz 的选项非常有限。相比之下， SMO 可用的选项几乎与 SSMS 中的完全匹配，表明它甚至可能是相同的代码。 (我希望MS不要写两次!)MSDN上有几个例子，比如this one将脚本表显示为单独的对象。但是，如果您希望使用包含“DRI”(声明性引用完整性)对象(如外键)的“完整”模式正确编写所有内容，那么单独编写表脚本将无法正确解决依赖关系。我发现有必要收集所有 URN 并将它们作为数组交给脚本编写者。这段代码是根据示例修改的，对我有用(尽管我敢说您可以整理它并对其进行更多注释):

    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Sdk.Sfc;
    // etc...

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

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

    Scripter scrp = new Scripter(srv);
    scrp.Options.ScriptDrops = false;
    scrp.Options.WithDependencies = true;
    scrp.Options.Indexes = true;   // To include indexes
    scrp.Options.DriAllConstraints = true;   // to include referential constraints in the script
    scrp.Options.Triggers = true;
    scrp.Options.FullTextIndexes = true;
    scrp.Options.NoCollation = false;
    scrp.Options.Bindings = true;
    scrp.Options.IncludeIfNotExists = false;
    scrp.Options.ScriptBatchTerminator = true;
    scrp.Options.ExtendedProperties = true;

    scrp.PrefetchObjects = true; // some sources suggest this may speed things up

    var urns = new List<Urn>();

    // Iterate through the tables in database and script each one   
    foreach (Table tb in db.Tables)
    {
        // check if the table is not a system table
        if (tb.IsSystemObject == false)
        {
            urns.Add(tb.Urn);
        }
    }

    // Iterate through the views in database and script each one. Display the script.   
    foreach (View view in db.Views)
    {
        // check if the view is not a system object
        if (view.IsSystemObject == false)
        {
            urns.Add(view.Urn);
        }
    }

    // Iterate through the stored procedures in database and script each one. Display the script.   
    foreach (StoredProcedure sp in db.StoredProcedures)
    {
        // check if the procedure is not a system object
        if (sp.IsSystemObject == false)
        {
            urns.Add(sp.Urn);
        }
    }

    StringBuilder builder = new StringBuilder();
    System.Collections.Specialized.StringCollection sc = scrp.Script(urns.ToArray());
    foreach (string st in sc)
    {
        // It seems each string is a sensible batch, and putting GO after it makes it work in tools like SSMS.
        // Wrapping each string in an 'exec' statement would work better if using SqlCommand to run the script.
        builder.AppendLine(st);
        builder.AppendLine("GO");
    }

    return builder.ToString();