Script table into a single create statement

  • Is there a way to script tables into a single create statement instead of a create and then a few alter statements? I want the scripted table to come out like this:

    CREATE TABLE [dbo].

    (

    [Date] [smalldatetime] CHECK ([Date] >= '2008-01-01' and [Date] <= '2008-12-31') ,

    [ID] [int],

    [Item] [int]

    PRIMARY KEY (Date, ID, Item)

    ) ON [PRIMARY]

    GO

    Instead of like this:

    CREATE TABLE [dbo].

    (

    [Date] [smalldatetime] NOT NULL ,

    [ID] [int] NOT NULL ,

    [Item] [int] NOT NULL

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].

    WITH NOCHECK ADD

    CONSTRAINT [PK_table] PRIMARY KEY CLUSTERED

    (

    [Date],

    [ID],

    [Item]

    ) WITH FILLFACTOR = 85 ON [PRIMARY]

    GO

    ALTER TABLE [dbo].

    ADD

    CONSTRAINT [CC_check] CHECK ([Date] >= '2008-01-01' and [Date] <= '2008-12-31')

    GO

  • See 'Create Table' in BOL. Specifically, look under section F: Complete table definitions.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • What I am looking for is to generate a script from an existing table. You can script a table by right clicking on the table in EM, All Tasks, Generate SQL Script.

    Using this method generates a script with create table and alter table statements. I need this to be a single create statement.

  • I need this to be a single create statement.

    Why?

    --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

  • I need to create new tables for updatable partitioned views and would rather do a find and replace 2007 with 2008 than have to re-write all the create table statements.

  • Start in Enterprise Mangeler... uh... Enterprise Manager. 😀 Drill down to the database tables you want and select them all. Right click on any of the selected tables, select {All Tasks}, then select {Generate Script}... follow your nose after that.

    Be advised that if there are dependencies between the tables, you may have to change the order of the scripts created (or generate single scripts and control the order with a batch file).

    Also, be advised that if you have any named constraints that are not changed due to your Search'n'Replace, they will fail in the new script as already being present.

    --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

  • Thanks Jeff. In a post above I stated you can create scripts from EM. However, it generates scripts with alter statements for the PK and the constraints. The constraints must be in table's create statement and you cannot use alter statements to add constraints when using partitioned updateable views. Unfortunately, I have first hand experience on how this breaks the view. Looks like a long day of manipulating scripts. 🙁

  • Sorry about the EM thing... still trying to flatten out a crease in my brain with coffee...

    If you set the scripting options correctly in Query Analyzer, you can generate such scripts, albeit one at a time, there. In case you don't know how to start that process, press the {f8} key and follow your nose. Right click on any of the tables and a scripting menu will appear. Be sure to look at and set the desired scripting options in the [Scripting Options...} selection of that same menu.

    --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

  • Jeff you are a genius! Don't know why scripting from QA didn't cross my mind :crazy:

    I tried many different ways to script a table but missed QA. It works like a charm. Thanks.

  • Outstanding, Edogg! Thank you for the feedback!

    --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

  • Both EM and QA do their scripting using calls to a SQL scripting API. My experience is this can be buggy when you try to get everything scripted out as a single CREATE statement. It does not give an error, but some types of constraint syntax are just not scripted.

    You should cross-check the results of your scripting against the original tables to confirm everything you need is there.

    I found the only way to reliably get the entire table definition scripted was to use multiple calls to the scripting API, requesting a) the main table definition, b) constraints, c) indexes, d) triggers. This causes the constraints to be generated as ALTER statements, but at least you get them...

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Use 3rd party tools or see the below link, it may helps to you.

    http://www.microsoft.com/downloads/details.aspx?FamilyID=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

    🙂

  • EdVassie (11/29/2007)


    Both EM and QA do their scripting using calls to a SQL scripting API. My experience is this can be buggy when you try to get everything scripted out as a single CREATE statement.

    Ed,

    Are you talking about the sp_OA* routines or something else? If it something else, is it possible to run that "something else" either from T-SQL or at the command prompt using VBS? If so, I sure would like to learn how. Thanks...

    --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

  • I used to make available a bunch of scripts from my old place that helped in migrating from SQL 2000 to 2005, one of which scripted tables. As they have my old employer's name all over them I no longer feel free to distribute them. The COM routines needed to do the scripting can all be called from the sp_OA* procedures, but I called tham direct from VB.

    I am working on making them more generic and publishing them on this site but life is getting in the way of this work. I'll see if I can look at them at home and give more details.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Thanks, Ed... and I fully understand about the "company name" thing... I gave almost the same answer on a similar request for code. Nice to see some "ethics".

    --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

Viewing 15 posts - 1 through 15 (of 16 total)

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