CODEWITHSUNDEEP
sql-serverdatabasesql-server-express
mark
mark

Reputation: 62712

Generating Database Scripts in SQL Server Express

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.

Upvotes: 1

Views: 1995

Answers (1)

RickNZ
RickNZ

Reputation: 18654

  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.

Upvotes: 1

Related Questions