CODEWITHSUNDEEP
sqlsql-serverimportrelationshiprelation
David Müller
David Müller

Reputation: 97

Get order of sql server tables related to their relationships

is it possible to get an order of the tables in the SQL Server related to their relationships?

I thought, when i create insert Scrips, the ssms would set an order to the insert scripts. But they will be created alphabetically to the tablenames.

My Problem is, that i have to right an Import mechanism, to Import data. Now i have to know, with which tables i can begin and what tables has to be done before i can Import the next data.

In case of 200 tables it is a bit heavy to get an overview.

Maybe someone has an idea.

Thanks

David

Upvotes: 0

Views: 97

Answers (2)

David Brabant
David Brabant

Reputation: 43539

One option is to encapsulate your sql statements with:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
GO

<your sql statements here>

EXEC sp_msforeachtable @command1='ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
go

If you know exactly what you are doing ...

Upvotes: 0

Randy Minder
Randy Minder

Reputation: 48482

You're going to be much better off using a tool that was designed to do a job like this, especially with a DB that has 200 tables. There are some free tools that can do this. One that comes to mind is SSDT (SQL Server Data Tools). It does schema and data comparisons. I use this all the time. It knows how to properly order the changes so you don't get FK errors etc.

Red Gate makes very good commercial tools that do schema and data comparisons. But they are a tad pricey. Personally, unless you really want to write your own solution, I'd check into with something like SSDT. You're going to save yourself a lot of grief.

Upvotes: 1

Related Questions