How to know dependency on a table

Question

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?

Answer 1

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.

Answer 2

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
)