June 20, 2018 at 7:24 am
Hi,
How to find the entire table list in the order of Foreign key reference in a database
ie. Parent table first, then Child table
Regards
Binu
June 20, 2018 at 11:26 am
binutb - Wednesday, June 20, 2018 7:24 AMHi,How to find the entire table list in the order of Foreign key reference in a database
ie. Parent table first, then Child table
Regards
Binu
You can query sys.foreign_keys to get that information: SELECT
[name] ConstraintName,
OBJECT_NAME(parent_object_id) ReferencingTable,
OBJECT_NAME (referenced_object_id) ReferencedTable
FROM sys.foreign_keys
Sue
June 20, 2018 at 11:49 am
Hi,
sql is ok, but i wish to get sequential order of table for create one by one.
ie: if we create table one by one, which is create first, then next and so on.
Regards
Binu
June 20, 2018 at 11:57 am
binutb - Wednesday, June 20, 2018 11:49 AMHi,sql is ok, but i wish to get sequential order of table for create one by one.
ie: if we create table one by one, which is create first, then next and so on.Regards
Binu
Creation dates are available in that same catalog view. With very little effort, you should be able to modify that to add the columns, ordering you want. And then you will need to decide what to do when they have the same date since that can happen.
Sue
June 20, 2018 at 12:03 pm
Use a recursive CTE to order the foreign keys.
WITH FK AS (
SELECT RefTableId = referenced_object_id,
ChildTable = OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id),
ParentTable = OBJECT_SCHEMA_NAME(referenced_object_id) + '.' + OBJECT_NAME(referenced_object_id),
Tier = 0
FROM sys.foreign_keys
WHERE parent_object_id NOT IN (SELECT referenced_object_id FROM sys.foreign_keys)
UNION ALL
SELECT RefTableId = p.referenced_object_id,
ChildTable = c.ParentTable,
ParentTable = OBJECT_SCHEMA_NAME(p.referenced_object_id) + '.' + OBJECT_NAME(p.referenced_object_id),
Tier = c.Tier + 1
FROM FK c
INNER JOIN sys.foreign_keys p ON c.RefTableId = p.parent_object_id
WHERE p.parent_object_id <> p.referenced_object_id
)
SELECT FK.ChildTable, Tier = MAX(FK.Tier)
FROM FK
GROUP BY FK.ChildTable
ORDER BY Tier DESC
June 20, 2018 at 1:58 pm
Scott Coleman - Wednesday, June 20, 2018 12:03 PMUse a recursive CTE to order the foreign keys.
WITH FK AS (
SELECT RefTableId = referenced_object_id,
ChildTable = OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id),
ParentTable = OBJECT_SCHEMA_NAME(referenced_object_id) + '.' + OBJECT_NAME(referenced_object_id),
Tier = 0
FROM sys.foreign_keys
WHERE parent_object_id NOT IN (SELECT referenced_object_id FROM sys.foreign_keys)
UNION ALL
SELECT RefTableId = p.referenced_object_id,
ChildTable = c.ParentTable,
ParentTable = OBJECT_SCHEMA_NAME(p.referenced_object_id) + '.' + OBJECT_NAME(p.referenced_object_id),
Tier = c.Tier + 1
FROM FK c
INNER JOIN sys.foreign_keys p ON c.RefTableId = p.parent_object_id
WHERE p.parent_object_id <> p.referenced_object_id
)
SELECT FK.ChildTable, Tier = MAX(FK.Tier)
FROM FK
GROUP BY FK.ChildTable
ORDER BY Tier DESC
Does that really answer the poster who wants the parent table and child table listed and in sequence by creation? It lists a table name and a tier value and doesn't pick up all tables with foreign keys. Tables with two foreign keys have a tier value of 0, self referencing keys aren't listed at all, etc.
So where is the parent table and then the child table and the sequence by creation date?
Sue
June 20, 2018 at 11:27 pm
Hi,
below error when apply sql
Msg 530, Level 16, State 1, Line 3
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Regards
Binu
June 21, 2018 at 4:41 am
Hi,
yes , i want parent table and child table creation sequence.
Binu
June 21, 2018 at 6:39 am
Sue_H - Wednesday, June 20, 2018 1:58 PMDoes that really answer the poster who wants the parent table and child table listed and in sequence by creation? It lists a table name and a tier value and doesn't pick up all tables with foreign keys. Tables with two foreign keys have a tier value of 0, self referencing keys aren't listed at all, etc.
So where is the parent table and then the child table and the sequence by creation date?Sue
My query tells you what order the tables need to be created in. It doesn't script the tables or foreign keys for you.
If a table is referenced by another table, it will be in a higher tier and needs to be created first. A table may have 20 foreign keys, but still be in tier 0 because no other table referenced it. (Those 20 tables will be at least 1 tier higher however.)
The creation dates of the foreign keys and tables are irrelevant, the precedence constraint is that you can't create a table with a foreign key (assuming you're creating each table with its constraints, indexes, and keys together as one package) until the referenced table has been created.
A self-referencing foreign key does not affect the table creation order, obviously the table must exist before the self-referencing foreign key is created. It depends on no other table.
June 21, 2018 at 7:00 am
Scott Coleman - Thursday, June 21, 2018 6:39 AMSue_H - Wednesday, June 20, 2018 1:58 PMDoes that really answer the poster who wants the parent table and child table listed and in sequence by creation? It lists a table name and a tier value and doesn't pick up all tables with foreign keys. Tables with two foreign keys have a tier value of 0, self referencing keys aren't listed at all, etc.
So where is the parent table and then the child table and the sequence by creation date?Sue
My query tells you what order the tables need to be created in. It doesn't script the tables or foreign keys for you.
If a table is referenced by another table, it will be in a higher tier and needs to be created first. A table may have 20 foreign keys, but still be in tier 0 because no other table referenced it. (Those 20 tables will be at least 1 tier higher however.)
The creation dates of the foreign keys and tables are irrelevant, the precedence constraint is that you can't create a table with a foreign key (assuming you're creating each table with its constraints, indexes, and keys together as one package) until the referenced table has been created.
A self-referencing foreign key does not affect the table creation order, obviously the table must exist before the self-referencing foreign key is created. It depends on no other table.
Simply put - It does not solve the problem. SomeTable tier 4 doesn't help when needing to list the parent table and list the child table. And they want them for all foreign keys - not ignoring ones that you decide don't need to be listed.
You believe it is correct so help the poster out - they are trying to run your script. You should be able to walk them through getting what they want with that.
Sue
June 21, 2018 at 7:25 am
binutb - Wednesday, June 20, 2018 11:27 PMMsg 530, Level 16, State 1, Line 3
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
This will happen if you remove the WHERE clause from the recursive query in the CTE, as soon as it finds a self-referencing foreign key.
If you copied the query correctly and saw this, it must be a circular reference. I don't have a good answer for this situation, other than to create all the tables without foreign keys first and then create all the foreign keys separately.
June 21, 2018 at 9:07 am
Sue_H - Wednesday, June 20, 2018 11:57 AMbinutb - Wednesday, June 20, 2018 11:49 AMHi,sql is ok, but i wish to get sequential order of table for create one by one.
ie: if we create table one by one, which is create first, then next and so on.Regards
BinuCreation dates are available in that same catalog view. With very little effort, you should be able to modify that to add the columns, ordering you want. And then you will need to decide what to do when they have the same date since that can happen.
Sue
Creation dates may not help here. It's the hierarchical order of the FKs that's needed. The creation dates may be totally wonky compared to the FK hierarchy.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2018 at 10:59 am
Jeff Moden - Thursday, June 21, 2018 9:07 AMSue_H - Wednesday, June 20, 2018 11:57 AMbinutb - Wednesday, June 20, 2018 11:49 AMHi,sql is ok, but i wish to get sequential order of table for create one by one.
ie: if we create table one by one, which is create first, then next and so on.Regards
BinuCreation dates are available in that same catalog view. With very little effort, you should be able to modify that to add the columns, ordering you want. And then you will need to decide what to do when they have the same date since that can happen.
Sue
Creation dates may not help here. It's the hierarchical order of the FKs that's needed. The creation dates may be totally wonky compared to the FK hierarchy.
Yup...I'm aware of that. Modify date could suffer similar issues - it may or may not affect the creation order. Can't say I agree with the recursive CTE and the results of that.
But it is what it is.
Sue
June 22, 2018 at 2:42 am
Hi,
Finally we can conclude, unable to find table list in creation sequential order.
Please advise any possibility if availabel.
Regards
Binu
June 22, 2018 at 6:44 am
binutb - Friday, June 22, 2018 2:42 AMHi,Finally we can conclude, unable to find table list in creation sequential order.
Please advise any possibility if availabel.Regards
Binu
Stop and think about it. What enforces the required order of creation? FKs. If you simply wait until all of the tables are created before you apply the FKs, the order of table creation is unimportant.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply