table script to dump to csv with foreign key order

Question

table script to dump to csv with foreign key order

Bruin

SSCrazy

Points: 2058
More actions
June 17, 2021 at 3:25 pm
Go to Answer
#3897524

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.
Phil Parkin

SSC Guru

Points: 246960
More actions
June 18, 2021 at 1:03 pm
Answer
#3897974
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

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)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

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)

Bruin SSCrazy Points: 2058 More actions · Answer 3

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

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

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)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Bruin SSCrazy Points: 2058 More actions · Answer 7

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

Bruin SSCrazy Points: 2058 More actions · Answer 8

Bruin

SSCrazy

Points: 2058

June 22, 2021 at 6:08 pm

#3899409

Thanks that worked.