c# - Inconsistent default constraints from SQL Server Management Objects (SMO) -

c# - Inconsistent default constraints from SQL Server Management Objects (SMO) -

i have programme generates ddl scripts microsoft sql server database using sql server management objects (smo). however, depending on server , database, receive inconsistent output of default constraints tables. inline create table statement, , standalone alter table statements. realize both valid , right sql-statements, without consistency prevents automated comparing between output of multiple databases , prevents adding output source command track changes of database schema. how can ensure consistency in script output of default constraints?

sample program

the code should straight forward. opens server , database, generates individual script files each database object plus 1 more file contains script entire database. i've omitted lot of error checking , database objects appear 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 programme { 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 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 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 views, stored procedures, table triggers, view triggers, database triggers, etc // write file total ddl of 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 output looks when smo generates scripts inline statements 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] check add together constraint [fk_products_implementations] foreign key([implementationid]) references [dbo].[implementations] ([id]) alter table [dbo].[products] check constraint [fk_products_implementations] alter table [dbo].[products] check add together 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 output looks when smo generates scripts standalone statements 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 together constraint [df_products_type] default ((0)) [type] alter table [dbo].[products] add together constraint [df_products_managedtype] default ((0)) [managedtype] alter table [dbo].[products] check add together constraint [fk_products_implementations] foreign key([implementationid]) references [dbo].[implementations] ([id]) alter table [dbo].[products] check constraint [fk_products_implementations] alter table [dbo].[products] check add together constraint [fk_products_productfamilies] foreign key([productfamilyid]) references [dbo].[productfamilies] ([id]) alter table [dbo].[products] check constraint [fk_products_productfamilies]

never appears mixture within single database, can different output styles per database on single server. haven't noticed alter on time database perhaps haven't attempted generating scripts database on long plenty period of time. i've backed , restored database server , same server under different name , seems randomly decide take 1 output style. therefore, doesn't seem database setting when individual database restores can exhibit random behaviour.

currently servers used in testing have sql server 2012 installed , running code on same workstation sql server management studio 2012 installed. i've looked through properties of scriptingoptions on msdn , don't see stands out solution.

after farther investigation, have discovered issue sql server management objects (smo) , handling of default constraints in versions 2012 , above. others have reported related problems, such next microsoft connect issue: https://connect.microsoft.com/sqlserver/feedback/details/895113

while answers why default constraints sql server management objects (smo) inconsistent, isn't solution. it's possible determine workaround ensure consistency of output before microsoft gets around fixing issue. question still open other answers if can find workaround.

c# sql-server smo