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.