1

I generate change scripts for my database to keep it under source control, and a strange thing happens every time:

I have a FlowFolder table, and clicking Generate Scripts creates the following two scripts:

dbo.FlowFolder.Table.sql:

USE [NC]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FlowFolder](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [dbo].[ParentId] NULL,
    [ParentType] [dbo].[CLRTypeName] NOT NULL,
    [Name] [dbo].[EntityName] NOT NULL,
    [Description] [dbo].[EntityDescription] NULL,
    [LastChanged] [int] NULL,
 CONSTRAINT [PK_FlowFolder] 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
SET ANSI_PADDING OFF
GO

DF\_FlowFolder_LastChanged.Default.sql:

USE [NC]
GO
ALTER TABLE [dbo].[FlowFolder] ADD CONSTRAINT [DF_FlowFolder_LastChanged]  
            DEFAULT ((0)) FOR [LastChanged]
GO

Question

  1. Why does SQL Server Express produce two files?
  2. Why doesn't it place this constraint as a DEFAULT(0) attribute on the LastChanged field in the CREATE TABLE statement?
  3. How can I force SQL Server to generate a consolidated script for each change instead of splitting them up?

EDIT:

How we generate scripts. At first, it was a single file. But, unfortunately, SQLEXPRESS does not keep the order of the database entities from save to save. Meaning, that even a small change in the schema could result in a script widely different from the predecessor. This is very inconvenient if one wishes to compare the differences in schemas. Hence we adopted another approach. We generate script per database entity (not data, but schema entity, like table, user type, etc ...) and then apply a small utility that removes the comment inserted by SQLEXPRESS in each file stating the date of generation. After that it is clearly visible which schema entities have changed from revision to revision.

In conclusion, we must generate script per schema entity.

About the DEFAULT(0) constraints - we really do not need them to be named constraints, so placing them on the column definition is fine.

Improve this question
5
  • It appears we all edited the question. Let me know what you think about my edits and we'll go from there. Commented Dec 16, 2009 at 22:09
  • @George, do we no longer ask questions here? Must titles always be phrased in the form of a statement. Commented Dec 16, 2009 at 22:10
  • @Robert Harvey : There are multiple camps on whether the title should tell you what the post is about or whether it should ask the question. I believe the title should summarize the post but not ask the question. Commented Dec 16, 2009 at 22:12
  • I just counted the questions on the front page, and 35 out of 50 summarized the question, the other 15 actually posted the question in the title. That is completely unscientific; but it's a starting point. I'll have to check stackQL for the actual breakdown. Commented Dec 16, 2009 at 22:14
  • 2
    I thank all of you for being editors of my question. Any ideas on the matter itself? Commented Dec 16, 2009 at 22:59

1 Answer 1

Reset to default
1
  1. Do you have "File per object" selected on the output option panel of the wizard?
  2. Because you can't give constraints names when they're embedded in CREATE TABLE
  3. Make sure "Single file" is selected on the output option panel -- or try "Script to New Query Window"

Unfortunately, the feature you're looking for doesn't exist.

All constraints are given names -- even unnamed constraints are given names. SQL Server doesn't keep track of which names it created and which ones you created, so in the script generation process, it has to split them off.

Usually, it's better to manage this process in reverse. In other words, have a collection of script files that you combine together to create the DB. That way, you can have the script files structured however you want. Team System Data Edition does this all automatically for you. A regular DB project lets you keep them separate, but it's more work on the deployment side.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

OK, then is it possible to instruct SQLEXPRESS to keep all the ALTER TABLE statements in the same file with the CREATE TABLE? Because, I did not quite understand the logic here - some constraints get written to the same file with CREATE TABLE, while others - to dedicated files.
On the file side, the only control they provide is whether to split it up between multiple files or put it in one. In the multi-file case, you can't set which info goes in which files. To do that, you would need to write your own script generation program, using SMO.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.