Inconsistent default constraints from SQL Server Management Objects (SMO)

Question

I have program that generates DDL scripts for a Microsoft SQL Server database using SQL Server Management Objects (SMO). However, depending on the server and database, I receive inconsistent output of default constraints for tables. Sometimes they are inline with the CREATE TABLE statement, and sometimes they are standalone ALTER TABLE statements. I realize that both are valid and correct SQL-statements, but without consistency it prevents automated comparison between the output of multiple databases and prevents adding the output to source control to track changes of the database schema. How can I ensure consistency in the script output of default constraints?

Sample Program

The code should be straight forward. Opens the server and database, then generates individual script files for each database object plus one more file that contains a script for the entire database. I've omitted a lot of error checking and database objects that appear to generate consistent output already.

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);
        }
    }
}

Sample Output of Inline Statements

A sample of what the output looks like when SMO generates scripts with inline statements for default constraints.

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]

Sample Output of Standalone Statements

A sample of what the output looks like when SMO generates scripts with standalone statements for default constraints.

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]

Never appears to be a mixture within a single database, but can get different output styles per database on a single server. Haven't noticed it change over time for a database but perhaps I just haven't attempted generating scripts for a database over a long enough period of time. I've backed up and restored a database to another server and to the same server under a different name and it seems to randomly decide to choose one output style. Therefore, it doesn't seem like it could be a database setting when individual database restores can exhibit random behaviour.

Currently all servers used in testing have SQL Server 2012 installed and always running the code on the same workstation with SQL Server Management Studio 2012 installed. I've looked through the properties of ScriptingOptions on MSDN and I don't see anything that stands out as a solution.

Answer 1

This workaround modifies the scripts that are generated by removing the individual ALTER TABLE ... ADD CONSTRAINT ... DEFAULT scripts and putting the definitions in the CREATE TABLE script. It gets the "works on my machine" badge.

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));
}

Answer 2

After further investigation, I have discovered this is an issue with SQL Server Management Objects (SMO) and its handling of default constraints in versions 2012 and above. Others have reported related problems, such as the following Microsoft Connect issue: https://connect.microsoft.com/SQLServer/Feedback/Details/895113

While this answers why the default constraints from SQL Server Management Objects (SMO) are inconsistent, it isn't a solution. It's possible someone could determine a workaround to ensure consistency of the output before Microsoft gets around to fixing the issue. Thus this question is still open to other answers if you can find a workaround.

Answer 3

I guess I have found the workaround. The only thing we have to do is to set internal field forceEmbedDefaultConstraint of DefaultConstraint class to true. To do so we have to use some reflection. Please execute code below on every table you wish to script and the default constraint definition will be added to column creation statement independently on rows count.

    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);
            }
        }
    }

For people asking for explanation why do I think it should work: Using dotPeek I have found a method in Microsoft.SqlServer.SMO.Column class:

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));
}

Code above convinced me to change the value of forceEmbedDefaultConstraint to true. In my case it worked, but the order of database objects creation may influence the final result.