Generate Scripts that work?

Question

Post reply

Generate Scripts that work?

Jasmine D. Adamson

Hall of Fame

Points: 3428
More actions
April 1, 2008 at 2:03 pm

#204989

I am currently trying to add scripts for all database objects to our source control. I would like to use the scripting features provided in order to script all the objects automatically. I can do this, but the problem is - the scripts generated are wrong, contain syntax errors, and in general, just don't work. For years I have been wondering why this is and how to fix it, but usually I just work around the issue and get on with my life. Well, no more... I need this thing to work right! How can I get it generate scripts that are correct?
Example (SQL Server generates this):
/****** Object: Table [dbo].[Classes] Script Date: 04/01/2008 13:58:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Classes](
[ClassID] [int] IDENTITY(1,1) NOT NULL,
[ClassName] [varchar](50) NULL,
CONSTRAINT [PK_Classes] PRIMARY KEY CLUSTERED
(
[ClassID] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
IMO, this is a stupid waste of space... but I could put up with it if the script would actually run. What I would really like is some nice, clean SQL code, without any BS! Like this:
CREATE TABLE dbo.Classes(
ClassID int IDENTITY(1,1) primary key clustered,
ClassName varchar(50) NULL
)
Is that too much to ask? (Sorry I'm a little annoyed - I would much rather have 4 lines of readable, runnable code, than whatever that other script is...)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

I don't see anything wrong with the first script, and it ran clean for me.

I don't really like your script, since you are letting SQL Server name a database object (the primary key) for you.

Jasmine D. Adamson Hall of Fame Points: 3428 More actions · Answer 2

I think I was connected to a wrong version of SQL Server. It was saying syntax error near '('...

You could easily give the PK a name... the point is that the second script is not cluttered up with useless characters and is much more readable. Personally I don't have an issue with the database generating a name for that object - I don't subscribe to the notion of everything having an explicitly chosen name. The PK can easily be figured out without a name - it is the PK of "x" table - it doesn't really need a name.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

Your objections to the scripts SQL Server produces seem trivial, and more a matter of personal choice than any actual problem with them.

I have never had any problems running the scripts SQL Server produces.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Look at the scripting options - a lot of that "junk" you mention can be turned off. Of course - as has been mentioned - not all of it is junk...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

... and then do a searh'n'replace to kill the brackets, if you'd like.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sorin Comanescu SSC Rookie Points: 33 More actions · Answer 6

You might want to write your own .NET generator, there's a nice SMO library out there.

I made something like this for my own needs so it's a bit specialized but it might give you a jump start on SMO, just drop me a mail if you want the project sources.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Jasmine D. Adamson (4/1/2008)
I think I was connected to a wrong version of SQL Server. It was saying syntax error near '('...
You could easily give the PK a name... the point is that the second script is not cluttered up with useless characters and is much more readable. Personally I don't have an issue with the database generating a name for that object - I don't subscribe to the notion of everything having an explicitly chosen name. The PK can easily be figured out without a name - it is the PK of "x" table - it doesn't really need a name.

Also - for what it's worth: the PK is explicitly named this way because other objects might rely on it (since it's also the clustered index). It would really *suck* if the create scripts just got creative with your index names and constraint names and you happened to have query hints in place calling for specific indexes. Until you have a hint that mentions just using whatever the Clustered index was is with not explicit name, I would WANT it to explicitly name it during a create script.

Does it seem to write out a lot? Sure, but you have to remember that the purpose of this is to replicate what you have, not necessarily to make something pretty and pleasing.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?