Sqlserver: How to Sort Table Names Ordered by Their Foreign Key Dependency

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

    example:

    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));

---

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
;

SQLServer: How to sort table names ordered by their foreign key dependency

Thank you for a working solution NXC. You put me on the right track to solve the problem using a recursive CTE.

WITH 
  TablesCTE(TableName, TableID, Ordinal) AS
  (
  SELECT 
    OBJECT_SCHEMA_NAME(so.id) +'.'+ OBJECT_NAME(so.id) AS TableName,
    so.id AS TableID,
    0 AS Ordinal
  FROM dbo.sysobjects so INNER JOIN sys.all_columns ac ON so.ID = ac.object_id
  WHERE
    so.type = 'U'
  AND
    ac.is_rowguidcol = 1
  UNION ALL
  SELECT 
    OBJECT_SCHEMA_NAME(so.id) +'.'+ OBJECT_NAME(so.id) AS TableName,
    so.id AS TableID,
    tt.Ordinal + 1 AS Ordinal
  FROM 
    dbo.sysobjects so 
    INNER JOIN sys.all_columns ac ON so.ID = ac.object_id
    INNER JOIN sys.foreign_keys f 
      ON (f.parent_object_id = so.id AND f.parent_object_id != f.referenced_object_id)
    INNER JOIN TablesCTE tt ON f.referenced_object_id = tt.TableID
  WHERE
    so.type = 'U'
  AND
    ac.is_rowguidcol = 1
)  
SELECT DISTINCT 
  t.Ordinal,
  t.TableName
  FROM TablesCTE t
  INNER JOIN 
    (
    SELECT 
      TableName as TableName,
      Max (Ordinal) as Ordinal
    FROM TablesCTE
    GROUP BY TableName
    ) tt ON (t.TableName = tt.TableName  AND t.Ordinal = tt.Ordinal)
ORDER BY t.Ordinal, t.TableName

For thoose wondering what this is useable for: I will use it to safely empty a database without violating any foreign key relations. (By truncating in descending order)
I will also be able to safely fill the tables with data from another database by filling the tables in ascending order.

How can I list all foreign keys referencing a given table in SQL Server?

Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table:

EXEC sp_fkeys 'TableName'

You can also specify the schema:

EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'

Without specifying the schema, the docs state the following:

If pktable_owner is not specified, the default table visibility rules
of the underlying DBMS apply.

In SQL Server, if the current user owns a table with the specified
name, that table's columns are returned. If pktable_owner is not
specified and the current user does not own a table with the specified
pktable_name, the procedure looks for a table with the specified
pktable_name owned by the database owner. If one exists, that table's
columns are returned.

Sorting database tables according to their FK's connections to determine creation order

See if this works.

DECLARE @IndependentTables TABLE (
    Id INT,
    Name VARCHAR(256)
)

DECLARE @Tables TABLE (
    Id INT,
    Name VARCHAR(256)
)

INSERT INTO @IndependentTables
SELECT object_id, NAME
FROM sys.tables
WHERE object_id NOT IN (SELECT parent_object_id FROM sys.foreign_keys)

INSERT INTO @Tables
SELECT * FROM @IndependentTables

DECLARE @id INT,
        @name VARCHAR(256)

DECLARE db_cursor CURSOR FOR 
SELECT DISTINCT tbl.object_id, tbl.name
FROM sys.foreign_keys keys
JOIN sys.tables tbl ON tbl.object_id = keys.referenced_object_id

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @id, @name

WHILE @@FETCH_STATUS = 0
BEGIN 
    -- Relations
    INSERT INTO @Tables
    SELECT tbl.object_id, tbl.name
    FROM sys.tables tbl
    JOIN (SELECT referenced_object_id
        FROM sys.foreign_keys
        WHERE parent_object_id = @id) keys ON keys.referenced_object_id = tbl.object_id
    WHERE tbl.object_id NOT IN (SELECT Id FROM @Tables)

    FETCH NEXT FROM db_cursor INTO  @id, @name
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

INSERT INTO @Tables
SELECT object_id, NAME
FROM sys.tables
WHERE object_id NOT IN (SELECT Id FROM @Tables)

SELECT Name FROM @Tables

---

Related Topics