Post reply

Generate Scripts that work?

  • 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...)

  • April 1, 2008 at 2:31 pm

    #796858

    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.

  • April 1, 2008 at 3:12 pm

    #796882

    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.

  • April 1, 2008 at 3:23 pm

    #796891

    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.

  • April 1, 2008 at 10:05 pm

    #796994

    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?

  • April 1, 2008 at 10:37 pm

    #797008

    ... 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)
    Intro to Tally Tables and Functions

  • April 3, 2008 at 7:18 am

    #797782

    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.

  • April 3, 2008 at 7:50 am

    #797814

    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?

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

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