question

ArnoKwetters avatar image
ArnoKwetters asked

How getting a Default Constraint per column ?

I make a table with Default values and that works fine : CREATE TABLE [TestDefault]( [ID] [int] NOT NULL, [aInt] [int] NOT NULL DEFAULT ((0)), [aTime] [datetime] NOT NULL DEFAULT (getdate()), [aBit] [bit] NULL DEFAULT ((0)), CONSTRAINT [PK_Logging.NotificationLog] 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] GO But when I use "Script Table as" / Create To / New query Window then I get this : USE [DB] GO /****** Object: Table [dbo].[TestDefault] Script Date: 21-5-2015 14:54:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TestDefault]( [ID] [int] NOT NULL, [aInt] [int] NOT NULL, [aTime] [datetime] NOT NULL, [aBit] [bit] NULL, CONSTRAINT [PK_Logging.NotificationLog] 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] GO ALTER TABLE [dbo].[TestDefault] ADD DEFAULT ((0)) FOR [aInt] GO ALTER TABLE [dbo].[TestDefault] ADD DEFAULT (getdate()) FOR [aTime] GO ALTER TABLE [dbo].[TestDefault] ADD DEFAULT ((0)) FOR [aBit] GO How do I get my original Defaults on the line of the ColumnNames ?
constraintdefault
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

At the risk of sounding flippant - why does this matter? It achieves the same result.
0 Likes 0 ·

1 Answer

·
David Wimbush avatar image
David Wimbush answered
I think the scripting tool iterates through the columns and outputs that part, and then iterates through the constraints and adds those. Otherwise it would have to collect both and match them up. It's just easier this way. Your script is clearer but SQL Server won't remember how you did it and give you that back. For readability I prefer to take my own script and check it into source control.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks David for your fast reply, It is very strange but I have very much tables and almost all tables generates the 'wrong'script. But I have a few tables which are doing what I want ! I found a difference : SET ANSI_PADDING ON GO Does that make sense ?
0 Likes 0 ·
I don't think that option will make any difference to the way the scripting engine works. If you want your version of the script why not just keep that? It also documents how you created the table.
1 Like 1 ·
Eureka !! I found it myself. If there is data in the table then the 'right' script will appear. If there is no data in the table then the 'wrong' script be generated. Thanks for looking with me.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.