Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a Database Called Total and I have about 40 tables in it.I am trying to move the tables in the database into a different server So I have prepared all the create table scripts but the problem is all the tables have foreign keys so I want to know the order in which I have to execute the tables.

Is there any way to know it?

share|improve this question

migrated from stackoverflow.com Oct 5 '11 at 13:35

This question came from our site for professional and enthusiast programmers.

2 Answers 2

You should do it in two passes:

First create the tables:

create table Table1 (..., constraint pk_table1 primary key (...));
create table Table2 (..., constraint pk_table2 primary key (...));
..
create table TableN (..., constraint pk_tableN primary key (...));

Then add the foreign key constraints:

alter table Table1 add constraint fk_table1_tablex 
    foreign key (...) 
    references TableX (...);
alter table Table2 add constraint fk_table2_tabley 
    foreign key (...) 
    references TableY (...);
...

This way the there is no order dependency.

share|improve this answer
    
I have got the create scripts through SSMS and if i have to do it the way you told me then I need to seperate the foreign key constarints seperate which i cannot do since there are lot of tables –  peter Oct 4 '11 at 22:16
    
Peter, you could generate table scripts separately form the constraint scripts. Honestly though, 40 tables is not a huge amount. –  datagod Oct 5 '11 at 14:26

Here are all the tables that are not referenced by a foreign key:

select name
from sys.tables
where type = 'u'
and object_id not in
(
  select referenced_object_id
  from sys.foreign_key_columns
)

That will give you a list of all tables that are not referenced. To get a list of tables that are referenced (and should be created first), here would be the code:

select name
from sys.tables
where type = 'u'
and object_id in
(
  select referenced_object_id
  from sys.foreign_key_columns
)

To get a list of tables that do not have any foreign key constraint (and therefore no dependency) run this code:

select name
from sys.tables
where type = 'u'
and object_id not in
(
  select parent_object_id
  from sys.foreign_key_columns
)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.