Arquivo da categoria: SQL

How to list tables in their dependency order (based on foreign keys)?

Example:
[code type=sql]
create table t1 (i int primary key,j int unique)
create table t2 (i int primary key references t1 (i));
create table t3 (i int,j int,primary key (i,j));
create table t4 (i int,j int, foreign key (i,j) references t3 (i,j));
create table t5 (i int references t1 (i),j int,foreign key (i,j) references t3 (i,j));
create table t6 (i int references t2 (i));

Query:
[code type=sql]
WITH cte (lvl, object_id, NAME)
AS (SELECT 1,
object_id,
NAME
FROM sys.tables
WHERE type_desc = ‘USER_TABLE’
AND is_ms_shipped = 0
UNION ALL
SELECT cte.lvl + 1,
t.object_id,
t.NAME
FROM cte
JOIN sys.tables AS t
ON EXISTS (SELECT NULL
FROM sys.foreign_keys AS fk
WHERE fk.parent_object_id = t.object_id
AND fk.referenced_object_id = cte.object_id)
AND t.object_id <> cte.object_id
AND cte.lvl < 30 WHERE t.type_desc = 'USER_TABLE' AND t.is_ms_shipped = 0) SELECT NAME, Max (lvl) AS dependency_level FROM cte GROUP BY NAME ORDER BY dependency_level, NAME;

https://stackoverflow.com/questions/40388903/how-to-list-tables-in-their-dependency-order-based-on-foreign-keys