我有一个程序，可以使用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 + "rn");
sbTable.Append(line + "rn");
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[] { "rnGOrn" }, 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的属性，没有发现任何突出的解决方案。
经过进一步调查，我发现这是SQL Server管理对象（SMO）及其在2012及更高版本中对默认约束的处理存在的问题。其他人报告了相关问题，例如以下Microsoft Connect问题：https://connect.microsoft.com/SQLServer/Feedback/Details/895113
虽然这回答了SQL Server管理对象（SMO）的默认约束不一致的原因，但它不是一个解决方案。在微软着手解决问题之前，有人可能会确定一个解决方案，以确保输出的一致性。因此，如果你能找到一个变通办法，这个问题仍然可以得到其他答案。
此解决方法通过删除单个ALTER TABLE ... ADD CONSTRAINT ... DEFAULT脚本并将定义放入CREATE TABLE脚本来修改生成的脚本。它获得了"在我的机器上工作"徽章。
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 .+ ADDs+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));
}
我想我已经找到了解决方法。我们唯一要做的就是将DefaultConstraint类的内部字段forceEmbedDefaultConstraint设置为true。要做到这一点，我们必须进行一些反思。请对要编写脚本的每个表执行下面的代码，默认约束定义将根据行数独立添加到列创建语句中。
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);
}
}
}
对于那些要求解释为什么我认为它应该工作的人：使用dotPeek，我在Microsoft.SqlServer.SMO.Column类中找到了一个方法：
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));
}
上面的代码说服我将forceEmbedDefaultConstraint的值更改为true。在我的案例中，它起了作用，但数据库对象的创建顺序可能会影响最终结果。