table script to dump to csv with foreign key order

  • Can PS be used to dump the SQL DDL to csv but do it in the order which would used to reload an empty database.. Tables

    have lots of foreign key constraints .  I would like to produce a file that I could use to rebuild an empty database, but foreign keys are causing me an issue.. I used a PS script that dumped SQL objects by type, but can it be scripted to have a "LOAD ORDER" of the tables.

    Thanks.

  • If you only need to do this occasionally, it might just be simpler to open the Object Explorer window in SSMS (press the {f8} key to open it if it's not already open) and then right-click on the database and select {Tasks}{Generate Scripts} and then follow your nose.

    It's a real shame that MS doesn't have a stored procedure or other easily programmable method to do this.  They did have an EXE file you could call a long time ago but they took that away a long time ago.

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

  • p.s.  I also wouldn't use the "data" feature of the above method because it creates and INSERT/VALUEs statement for every bloody row of data.

    If you use a product like RedGate to do this for you, you'll have a similar issue with data.

    --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 have used that before.. but was looking for something that would dump tables in order with foreign key reference in mind, so I could pickup this file and create a new DB\tables without have to do backup-restore...

    Thanks..

  • Are you looking for a way to build a new database with no data - or do you need data also?  If you want a database with the schema only - then take a look at DBCC CLONEDATABASE.  Make sure to review the parameters available - as you would want to set the options for production and backup as that creates a production ready backup that can be restored to another instance.

    If you need data pre-populated then you will need to identify that data and export to a file that can be used to import the data.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Bruin wrote:

    I have used that before.. but was looking for something that would dump tables in order with foreign key reference in mind, so I could pickup this file and create a new DB\tables without have to do backup-restore...

    Thanks..

    Maybe I just got lucky in the past but I've not had the issue you speak of with it before.  Knowing MS, it's probably the former. 😀

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

  • Bruin wrote:

    I have used that before.. but was looking for something that would dump tables in order with foreign key reference in mind, so I could pickup this file and create a new DB\tables without have to do backup-restore...

    Thanks..

    Have you considered using a DACPAC to do this?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • What ever will work to be able to load a new copy of the DB with Foreign Key in mind during the process. I want basically a schema only

    version of the DB, and thought PS would be able to sync the table loads based on Foreign Key constraints ..

    Load tbl1 before tbl3 based on FK constraints type of logic

    Thanks All replies so far...

     

  • DACPAC will do that. If you are not familiar, there is plenty of material available online. The process should be straightforward:

    1. 'Extract' a DACPAC from the existing database.
    2. Create a new database using the extracted DACPAC.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks that worked.

Viewing 10 posts - 1 through 9 (of 9 total)

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