Tested in 2008 R2, 2012 and 2014 all on the latest SP + CU

When  I generate as script from SSMS for the database tables via Generate Scripts there is an issue, when you generate the script for the database objects and there is data in the table with the options set to false for "Constraints and Defaults.

Scripting defaults = false (This always outputs when there is data, I think this is where the bug is)
Scripting Check Constraints = False ( had this also set, but didn't make any difference if it was on or off)

This is my test tables, table A has data and Table B has "no data".

Issue also exists in Powershell using SMO to generate a script for the tables

I don't want to output the Default values in the script? But if there is data then it will output the defaults as shown below.

How do I fix?

This is the output:

USE [tktest1]
GO
/****** Object:  Table [dbo].[TableA]    Script Date: 2015-04-18 9:16:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableA](
 [TableID] [int] IDENTITY(1,1) NOT NULL,
 [Col1] [varchar](50) NULL,
 [Col2] [varchar](50) NULL CONSTRAINT [DF_TableA_Col2]  DEFAULT ('Y'),
 CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED 
(
 [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[TableB]    Script Date: 2015-04-18 9:16:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableB](
 [TableID] [int] IDENTITY(1,1) NOT NULL,
 [Col1] [varchar](50) NULL,
 [Col2] [varchar](50) NULL,
 CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED 
(
 [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

This is using the Script table as from SSMS:

Table A

USE [tktest1]
GO

/****** Object:  Table [dbo].[TableA]    Script Date: 2015-04-18 10:07:41 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TableA](
	[TableID] [int] IDENTITY(1,1) NOT NULL,
	[Col1] [varchar](50) NULL,
	[Col2] [varchar](50) NULL CONSTRAINT [DF_TableA_Col2]  DEFAULT ('Y'),
 CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED 
(
	[TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Table B

USE [tktest1]
GO

/****** Object:  Table [dbo].[TableB]    Script Date: 2015-04-18 9:23:02 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TableB](
	[TableID] [int] IDENTITY(1,1) NOT NULL,
	[Col1] [varchar](50) NULL,
	[Col2] [varchar](50) NULL,
 CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED 
(
	[TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[TableB] ADD  CONSTRAINT [DF_TableB_Col2]  DEFAULT ('Y') FOR [Col2]
GO

Hi Trevor Kohlman,

Scripting Check Constraints this option adds "CHECK constraints" to the script, since there's no "CHECK constraint" in your table definition, so this option doesn't make sense here. If you what want to exclude is the PRIMARY KEY constraint, you can set the option "Script Primary Keys" false.

Regarding Scripting defaults, I was trying to create 2 tables with the "output" definition in your post and insert some rows into the table whose col2 with DEFAULT('Y') in SSMS. Setting Scripting defaults false, The script for the Table which has a DEFAULT('Y') is generated well, namely without "ALTER TABLE ADD .. DEFAULT ('Y') FOR [Col2]" appended.

So from my side, I am not able to reproduce the issue in SSMS. Since you have the same issue in Power shell with SMO, the culprit of your issue can be the old version of your SMO(SSMS calls SMO as well). Can you please check the version of your SMO(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SharedManagementObjects\CurrentVersion\Version or check the C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll "details" tab of  properties)?

In my scenario, the SMO is 12.0.2000.8 installed along with SSMS 2012 installation. If the SMO version obsolescence is the problem, you can install a newer SSMS or upgrade SMO SDK by installing SharedManagementObjects.msi.

If you don't want make any change on your SDK, as a workaround, use CREATE DEFAULT to set column default value. The default value set by this way is not included in the generated table script, regardless of the option Scripting defaults set true or false.

CREATE DEFAULT dflt AS 'Y';

sp_bindefault 'dflt', 'DBO.TableA.col2';

For more details about Generate SQL Server Scripts Wizard, you can click here.

If you have any feedback on our support, please click here.

I have tried this on 3 different box's. 1 Production and 2 dev box's.

Production box has 2008 R2 SP3 Feature pack SharedManagementObjects.msi . remote client has 2008 R2 SP3 Feature pack SharedManagementObjects.msi and 2012 SP2+CU5 + S2 Feature pack SharedManagementObjects.msi.  the client is where I run the powershell script and the SSMS script.

This version of SMO is 11.2.5058.0

Dev box 1 has 2008 R2 SP3 Feature pack SharedManagementObjects.msi and 2012 SP2+CU5 + S2 Feature pack SharedManagementObjects.msi

On this box the version of SMO is 11.2.5058.0.

Dev box 1 has 2008 R2 SP3 Feature pack SharedManagementObjects.msi , 2014 Feature pack SharedManagementObjects.msi

Just installed the SMO for 2014 Version 12.0.2000.8. and retested below.

-----------------------

for the Create default method I can not use as the Production system code would all need to be change.  and this won't happen anytime soon.  Lots of database and lots of tables in the database.  Good to know I have options.

--------

Table A and Table B are the same.  Both have the defaults set on the table at create time.

Only diff is one has data and one does not have data.

Here is the Create script I used for Table A and Table B.

1st test:  No rows in table A or B, Generate script with script defaults set to false.  Script does not generate defaults.

2nd test: Add one row to Table A, Generate script with script defaults set to false. script generates defaults in the script.

CREATE TABLE [dbo].[TableA](
 [TableID] [int] IDENTITY(1,1) NOT NULL,
 [Col1] [varchar](50) NULL,
 [Col2] [varchar](50) NULL CONSTRAINT [DF_TableA_Col2]  DEFAULT ('Y'),
 CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED 
(
 [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

----
CREATE TABLE [dbo].[TableB](
 [TableID] [int] IDENTITY(1,1) NOT NULL,
 [Col1] [varchar](50) NULL,
 [Col2] [varchar](50) NULL CONSTRAINT [DF_TableB_Col2]  DEFAULT ('Y'),
 CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED 
(
 [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

---For SSMS when you script table as Create To> for Table A and Table B.

Why does table A with data add the default Constraint in the output and Table B with no data adds the Alter table Add constraint at the end?

To me it should have created both the same.(as I shown above in the first post ,using the Script table as from SSMS: Table A and Table B)

I see the issue on 3 different box's before and after upgrading SMO on all of them.

here is a very similar issue for smo in powershell(https://connect.microsoft.com/SQLServer/feedback/details/745566/smo-2012-disregards-default-constraint)

Thanks

Trevor

Hi Trevor,

I am trying to involve someone more familiar with this topic for a further look at this issue. Some time delay might be expected from the job transferring.

Your patience is greatly appreciated. Thank you for your understanding and support. If you have any feedback on our support, you can click here.

Hi Trevor,

I did the same test with your script both in SQL Server 2008 R2, and 2012. But the functions works fine. The behaviors are the same for tables with data and without data.

If Script Defaults: False. No default value added.

If Script Defaults: True, a separated statement generated as below:

ALTER TABLE [dbo].[TableA] ADD  CONSTRAINT [DF_TableA_Col2]  DEFAULT ('Y') FOR [Col2]
GO

And I checked the bug system, there is no bug related with this.

First, can you help to run SELECT @@VERSION and paste the result to us?

Then, would you help double check the summary of the Generate Script? Make sure the Script Defaults is False.

Third, Can you try to create a new simple table to retest it?

If you insist it is a bug and can stably reproduced, please try to report it in https://connect.microsoft.com/. The bug cannot be handled here.

Thanks,

Wynn

• Edited by SQL Team - MSFTMicrosoft employee 20 minutes ago