SQL Server管理对象(SMO)的默认约束不一致

我有一个程序，可以使用SQL Server管理对象（SMO）为Microsoft SQL Server数据库生成DDL脚本。然而，根据服务器和数据库的不同，我收到的表的默认约束的输出不一致。有时它们与CREATE TABLE语句内联，有时它们是独立的ALTER TABLE语句。我意识到两者都是有效和正确的SQL语句，但如果没有一致性，它会阻止多个数据库的输出之间的自动比较，并阻止将输出添加到源代码管理中以跟踪数据库模式的更改如何确保默认约束的脚本输出的一致性

示例程序

代码应该是直接的。打开服务器和数据库，然后为每个数据库对象生成单独的脚本文件，再加上一个包含整个数据库脚本的文件。我省略了许多错误检查和数据库对象，这些对象似乎已经生成了一致的输出。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
using System.Runtime.Serialization;
using System.Data;
namespace Stackoverflow.Sample
{
    class Program
    {
        public static void CreateScripts(SqlConnectionStringBuilder source, string destination)
        {
            Server sv = new Server(source.DataSource);
            sv.ConnectionContext.LoginSecure = false;
            sv.ConnectionContext.Login = source.UserID;
            sv.ConnectionContext.Password = source.Password;
            sv.ConnectionContext.ConnectionString = source.ConnectionString;
            Database db = sv.Databases[source.InitialCatalog];
            ScriptingOptions options = new ScriptingOptions();
            options.ScriptData = false;
            options.ScriptDrops = false;
            options.ScriptSchema = true;
            options.EnforceScriptingOptions = true;
            options.Indexes = true;
            options.IncludeHeaders = true;
            options.ClusteredIndexes = true;
            options.WithDependencies = false;
            options.IncludeHeaders = false;
            options.DriAll = true;
            StringBuilder sbAll = new StringBuilder();
            Dictionary<string, TriggerCollection> tableTriggers = new Dictionary<string, TriggerCollection>();
            Dictionary<string, TriggerCollection> viewTriggers = new Dictionary<string, TriggerCollection>();
            // Code omitted for Functions
            // Tables
            foreach (Table table in db.Tables)
            {
                StringBuilder sbTable = new StringBuilder();
                foreach (string line in db.Tables[table.Name].Script(options))
                {
                    sbAll.Append(line + "'r'n");
                    sbTable.Append(line + "'r'n");
                    Console.WriteLine(line);
                }
                // Write file with DDL of individual object
                File.WriteAllText(Path.Combine(destination, table.Name + ".sql"), sbTable.ToString());
                if (table.Triggers.Count > 0)
                    tableTriggers.Add(table.Name, table.Triggers);
            }
            // Code omitted for Views, Stored Procedures, Table Triggers, View Triggers, Database Triggers, etc
            // Write file with full DDL of everything above
            string[] statements = sbAll.ToString().Split(new string[] { "'r'nGO'r'n" }, StringSplitOptions.RemoveEmptyEntries);
            File.WriteAllLines(Path.Combine(destination, "Full.sql"), statements);
        }
    }
}

内联语句的示例输出

当SMO为默认约束生成带有内联语句的脚本时，输出的示例。

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Products](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [StartDate] [date] NOT NULL,
    [EndDate] [date] NULL,
    [Name_En] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Name_Fr] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Type] [int] NOT NULL CONSTRAINT [DF_Products_Type]  DEFAULT ((0)),
    [ManagedType] [int] NOT NULL CONSTRAINT [DF_Products_ManagedType]  DEFAULT ((0)),
    [ProductFamilyID] [bigint] NOT NULL,
    [ImplementationID] [bigint] NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_Implementations] FOREIGN KEY([ImplementationID])
REFERENCES [dbo].[Implementations] ([ID])
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Implementations]
ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_ProductFamilies] FOREIGN KEY([ProductFamilyID])
REFERENCES [dbo].[ProductFamilies] ([ID])
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_ProductFamilies]

独立语句的示例输出

当SMO为默认约束生成带有独立语句的脚本时，输出的示例。

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Products](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [StartDate] [date] NOT NULL,
    [EndDate] [date] NULL,
    [Name_En] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Name_Fr] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Type] [int] NOT NULL,
    [ManagedType] [int] NOT NULL,
    [ProductFamilyID] [bigint] NOT NULL,
    [ImplementationID] [bigint] NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Products] ADD  CONSTRAINT [DF_Products_Type]  DEFAULT ((0)) FOR [Type]
ALTER TABLE [dbo].[Products] ADD  CONSTRAINT [DF_Products_ManagedType]  DEFAULT ((0)) FOR [ManagedType]
ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_Implementations] FOREIGN KEY([ImplementationID])
REFERENCES [dbo].[Implementations] ([ID])
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Implementations]
ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_ProductFamilies] FOREIGN KEY([ProductFamilyID])
REFERENCES [dbo].[ProductFamilies] ([ID])
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_ProductFamilies]

在单个数据库中看起来从来都不是混合的，但在单个服务器上每个数据库可以获得不同的输出样式。我没有注意到数据库会随着时间的推移而变化，但也许我只是在足够长的一段时间内没有尝试为数据库生成脚本。我已经将一个数据库备份并恢复到另一台服务器，并以不同的名称恢复到同一台服务器上，它似乎随机决定选择一种输出样式。因此，当单个数据库恢复可能表现出随机行为时，它似乎不可能是数据库设置。

目前，测试中使用的所有服务器都已安装SQL Server 2012，并且始终在安装了SQL Server Management Studio 2012的同一工作站上运行代码。我浏览了MSDN上ScriptingOptions的属性，没有发现任何突出的解决方案。

Table table = GetTable();
List<string> scripts = table.Script(new ScriptingOptions
{
    DriAll = true,
    FullTextCatalogs = true,
    FullTextIndexes = true,
    Indexes = true,
    SchemaQualify = true
}).Cast<string>().ToList();
// There is a bug in the SQL SMO libraries that changes the scripting of the
// default constraints depending on whether or not the table has any rows.
// This hack gets around the issue by modifying the scripts to always include
// the constaints in the CREATE TABLE definition. 
// https://connect.microsoft.com/SQLServer/Feedback/Details/895113
//
// First, get the CREATE TABLE script to modify.
string originalCreateTableScript = scripts.Single(s => s.StartsWith("CREATE TABLE"));
string modifiedCreateTableScript = originalCreateTableScript;
bool modificationsMade = false;
// This pattern will match all ALTER TABLE scripts that define a default constraint.
Regex defineDefaultConstraintPattern = new Regex(@"^ALTER TABLE .+ ADD's+CONSTRAINT '[(?<constraint_name>[^']]+)]  DEFAULT (?<constraint_def>.+) FOR '[(?<column>.+)]$");
// Find all the matching scripts.
foreach (string script in scripts)
{
    Match defaultConstraintMatch = defineDefaultConstraintPattern.Match(script);
    if (defaultConstraintMatch.Success)
    {
        // We have found a default constraint script. The following pattern
        // will match the line in the CREATE TABLE script that defines the
        // column on which the constraint is defined.
        Regex columnPattern = new Regex(@"^(?<def1>'s*'[" + Regex.Escape(defaultConstraintMatch.Groups["column"].Value) + @"].+?)(?<def2>,?'r)$", RegexOptions.Multiline);
        // Replace the column definition with a definition that includes the constraint.
        modifiedCreateTableScript = columnPattern.Replace(modifiedCreateTableScript, delegate (Match columnMatch)
        {
            modificationsMade = true;
            return string.Format(
                "{0} CONSTRAINT [{1}]  DEFAULT {2}{3}",
                columnMatch.Groups["def1"].Value,
                defaultConstraintMatch.Groups["constraint_name"].Value,
                defaultConstraintMatch.Groups["constraint_def"].Value,
                columnMatch.Groups["def2"].Value);
        });
    }
}
if (modificationsMade)
{
    int ix = scripts.IndexOf(originalCreateTableScript);
    scripts[ix] = modifiedCreateTableScript;
    scripts.RemoveAll(s => defineDefaultConstraintPattern.IsMatch(s));
}

    private void ForceScriptDefaultConstraint(Table table)
    {
        foreach (Column column in table.Columns)
        {
            if (column.DefaultConstraint != null)
            {
                FieldInfo info = column.DefaultConstraint.GetType().GetField("forceEmbedDefaultConstraint", BindingFlags.NonPublic | BindingFlags.GetField | BindingFlags.Instance);
                info.SetValue(column.DefaultConstraint, true);
            }
        }
    }

private void ScriptDefaultConstraint(StringBuilder sb, ScriptingPreferences sp)
{
  if (this.DefaultConstraint == null || this.DefaultConstraint.IgnoreForScripting && !sp.ForDirectExecution || (!this.EmbedDefaultConstraints() && !this.DefaultConstraint.forceEmbedDefaultConstraint || sb.Length <= 0))
    return;
  this.DefaultConstraint.forceEmbedDefaultConstraint = false;
  sb.Append(this.DefaultConstraint.ScriptDdl(sp));
}