Post reply

Script to generate only constraints

  • August 9, 2007 at 1:50 pm

    #175829

    Hi,

    Does anyone know how to script only constraints ( pk, fk, defaults ) and indexes only and not the actual table creation script?

    Thanks,

     

  • August 10, 2007 at 2:53 am

    #725924

    Probably you have to use SMO to do this.

    ...and your only reply is slàinte mhath

  • August 10, 2007 at 7:14 am

    #725986

    Just a list or to generate the actual code to create the constraints?

    Also, why do you want to do this?  Might be important...

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

  • March 26, 2011 at 8:27 am

    #1303948

    I also need to do this.

    My situation is that I have several production databases that I need to add new constraints and PKs to for an update/upgrade to the related software.

    I don't want, or need all of the other stuff that the Generate scripts wizard produces. That only means that I have to edit out all of the Create Table stuff for nearly 200 tables.

    SQL Compare is not an option right now. All financial resources have been committed to the actual software upgrade.

    Thanks in advance for your assistance in input.

    Jerry

  • March 26, 2011 at 11:39 am

    #1303966

    Understood on the SQL Compare. Not sure I have anything simple to help. I know that generating all the tables en masse produces a script where FK's and the like are generated near the end which makes editing pretty easy but I don't recall what it does with other constraints.

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

  • March 26, 2011 at 12:10 pm

    #1303971

    You'll want to query the system views directly:

    select * from sys.key_constraints

    select * from sys.default_constraints

    select * from sys.foreign_keys

    select * from sys.check_constraints

    select * from sys.indexes

    It should be pretty obvious what each is for.

    The key_constraints view has both primary key and unique constraints. Join to sys.indexes, sys.index_columns and sys.columns for the columns involved.

    sys.foreign_keys - join to sys.foreign_key_columns and sys.columns to get the column information. Join to sys.tables for table information.

    The check_constraints and default_constraints have a definition column for what the constraint is.

    Join sys.indexes to sys.index_columns and sys.columns for the index definitions. Ensure you order it by the index_column_id, and watch out for the is_included_column for adding these.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 6 posts - 1 through 5 (of 5 total)

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