为什么 SMO 脚本程序不生成外键标头？

Question

我正在创建一个程序，以使用SQL Server 2008分发的SMO库生成数据库架构。

我已经得到了脚本器输出代码，当它配置为输出所有内容时，它与SQL Server Management Studio输出几乎相同，但有一个奇怪的例外：它不会输出它在底部生成的外键约束的注释标头，而SSMS可以。 谁能弄清楚这是为什么？ 这是我的代码：

private void btnExportScript_Click(object sender, EventArgs ea) {
    Server srv = setupConnection();
    // Reference the database
    if (!srv.Databases.Contains(cbChooseDb.SelectedItem.ToString())) {
        _utils.ShowError("Couldn't find DB '" + cbChooseDb.SelectedItem.ToString() + "'.");
        return;
    }
    Database db = srv.Databases[cbChooseDb.SelectedItem.ToString()];
    StringBuilder builder = new StringBuilder();
    try {
        Scripter scrp = new Scripter(srv);
        scrp.Options.AppendToFile = false;
        scrp.Options.ToFileOnly = false;
        scrp.Options.ScriptDrops = false;             // Don't script DROPs
        scrp.Options.Indexes = true;                  // Include indexes
        scrp.Options.DriAllConstraints = true;        // Include referential constraints in the script
        scrp.Options.Triggers = true;                 // Include triggers
        scrp.Options.FullTextIndexes = true;          // Include full text indexes
        scrp.Options.NonClusteredIndexes = true;      // Include non-clustered indexes
        scrp.Options.NoCollation = false;             // Include collation
        scrp.Options.Bindings = true;                 // Include bindings
        scrp.Options.SchemaQualify = true;            // Include schema qualification, eg. [dbo]
        scrp.Options.IncludeDatabaseContext = false;
        scrp.Options.AnsiPadding = true;
        scrp.Options.FullTextStopLists = true;
        scrp.Options.IncludeIfNotExists = false;
        scrp.Options.ScriptBatchTerminator = true;
        scrp.Options.ExtendedProperties = true;
        scrp.Options.ClusteredIndexes = true;
        scrp.Options.FullTextCatalogs = true;
        scrp.Options.SchemaQualifyForeignKeysReferences = true;
        scrp.Options.XmlIndexes = true;
        scrp.Options.IncludeHeaders = true;
        // Prefectching may speed things up
        scrp.PrefetchObjects = true;
        var urns = new List<Urn>();
        // Iterate through the tables in database and script each one.
        foreach (Table tb in db.Tables) {
            if (tb.IsSystemObject == false) {
                // Table is not a system object, so add it.
                urns.Add(tb.Urn);
            }
        }
        // Iterate through the views in database and script each one.  Display the script.
        foreach (Microsoft.SqlServer.Management.Smo.View view in db.Views) {
            if (view.IsSystemObject == false) {
                // View is not a system object, so add it.
                urns.Add(view.Urn);
            }
        }
        // Iterate through the stored procedures in database and script each one.  Display the script.
        foreach (StoredProcedure sp in db.StoredProcedures) {
            if (sp.IsSystemObject == false) {
                // Procedure is not a system object, so add it.
                urns.Add(sp.Urn);
            }
        }
        // Start by manually adding DB context
        builder.AppendLine("USE [" + db.Name + "]");
        builder.AppendLine("GO");
        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.Append(st.Trim(new char[] { 'r', 'n' }) + "rnGOrn");
        }
    }
    catch (Exception ex) {
        showExceptionError("Couldn't generate script.", ex);
        return;
    }
    try {
        File.WriteAllText(txtExportToFile.Text, builder.ToString());
        _utils.ShowInfo("DB exported to script at: " + txtExportToFile.Text);
    }
    catch (Exception ex) {
        showExceptionError("Couldn't save script file.", ex);
        return;
    }
}

请注意，外键属于 DRI 约束类别，并且由于scrp.Options.DriAllConstraints = true;而编写脚本。

Answer 1

我这里有一个解决方案：无法让 EnumScript（） 生成约束

出于某种原因，当Scripter给定一个 Urn 列表时，它不会发出 DRI 约束（外键等），但如果一次给出一个 Urn，它就会发出。诀窍是按照瓮的祖先顺序给出骨灰盒：在约束引用表之前，必须定义表。为此，我使用了DependencyWalker.

以下是概要：

        var urns = new List<Urn>();
        Scripter schemaScripter = new Scripter(srv) { Options = schemaOptions };
        Scripter insertScripter = new Scripter(srv) { Options = insertOptions };
        var dw = new DependencyWalker(srv);
        foreach (Table t in db.Tables)
            if (t.IsSystemObject == false)
                urns.Add(t.Urn);
        DependencyTree dTree = dw.DiscoverDependencies(urns.ToArray(), true);
        DependencyCollection dColl = dw.WalkDependencies(dTree);
        foreach (var d in dColl)
        {
            foreach (var s in schemaScripter.Script(new Urn[] { d.Urn }))
                strings.Add(s);
            strings.Add("GO");
            if (scriptData)
            {
                int n = 0;
                foreach (var i in insertScripter.EnumScript(new Urn[] {d.Urn}))
                {
                    strings.Add(i);
                    if ((++n) % 100 == 0)
                        strings.Add("GO");
                }
            }
        }

注意：每隔一段时间添加"GO"会使批大小保持较小，因此 SSMS 不会耗尽内存。