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

Question

我有一个程序可以使用 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 的同一工作站上运行代码。我查看了 ScriptingOptions 的属性在 MSDN 上，我没有看到任何突出的解决方案。

Answer 1

经过进一步调查，我发现这是 SQL Server 管理对象 (SMO) 及其在 2012 及更高版本中处理默认约束的问题。其他人报告了相关问题，例如以下 Microsoft Connect 问题:https://connect.microsoft.com/SQLServer/Feedback/Details/895113

虽然这回答了为什么来自 SQL Server 管理对象 (SMO) 的默认约束不一致，但这不是解决方案。在 Microsoft 着手解决问题之前，有人可能会确定一种解决方法以确保输出的一致性。因此，如果您能找到解决方法，这个问题仍然可以找到其他答案。