Get foreign key hierarchy of all DB tables- Omri Bahat

/*

NAME:                Get foreign key hierarchy of all DB tables
                     (to determine tables INSERT or DROP order, for example)

DESCRIPTION:         This is a short script that returns all table names
                     in the current database, together with their foreign key (FK)
                     hierarchy level, and the table(s) that they reference (when
                     applicable). The value of the FK hierarchy associated with
                     each table is determined as follows: If a table does not
                     have a FK constraint (i.e., it does not reference any other
                     tables via a FK, or in other words - the table is not a foreign
                     table in any FK relationship), then it is of level 0 in the
                     hierarchy. If the table references one or more tables,
                     which do not reference any other tables, then the current
                     table is of level 1, and so on. The tables referenced by
                     each FK (i.e., primary tables) are returned by the script
                     as well, for each FK relationship found. Moreover, if a table
                     references itself (and no other tables), then it is
                     considered as a level 0 table.

                     The script is useful when one wishes to INSERT data into
                     several tables, or DROP tables, and needs to determine the
                     table order to follow - tables of hierarchy 0 must be
                     inserted into first, then those of hierarchy 1, and so on.
                     Similarly, tables with the highest hierarchy should be dropped
                     first, and those with hierarchy 0 should be dropped last.

                     To return the table FK hierarchy info, the script uses the
                     following algorithm: First, get all DB tables that do not
                     have any FK constraints. Then get all tables that have a
                     FK that only reference one or more of the tables that don't
                     have any FKs. Then, get the tables that have FKs mapped
                     to the already collected tables, and so on. The entire
                     algorithm is run in a simple WHILE loop.

USER PARAMETERS:     NA

RESULTSET:           TableName, HierarchyLevel, FKName, FKReference (the primary
                     table in the FK relationship, where applicable)

RESULTSET SORT:      NA

USING TABLES/VIEWS:  INFORMATION_SCHEMA.TABLES
                     sysreferences

REVISIONS


-- Get FK hierarchy of all DB tables

SET NOCOUNT ON

DECLARE @i INT
DECLARE @Cnt INT

-- The variable @i is the hierarchy level.
-- The variable @Cnt hold the number of tables returned in the
-- last run of the loop, which tells when the loop should exist.

SET @i = 0
SET @Cnt = 1

IF OBJECT_ID('tempdb..#tblFKTableOrder', 'U') IS NOT NULL
        DROP TABLE #tblFKTableOrder

CREATE TABLE #tblFKTableOrder (
        TableName NVARCHAR(128),
        HierarchyLevel INT,
        FKName NVARCHAR(128),
        FKReference NVARCHAR(128))
       

-- First, grab all the tables that don't have any FK constraints, as hierarchy level 0.

INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName, FKReference)
SELECT TABLE_NAME, @i, N'', N''
FROM INFORMATION_SCHEMA.TABLES WITH (NOLOCK)
WHERE TABLE_TYPE = 'BASE TABLE'
        AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasForeignKey') = 0


-- Second, get all tables that only have self-referencing (and no other) FKs.
-- In the query below - RS1 contains all table names that references themselves
-- (and possible other tables), and RS2 contains all tables that reference other tables.
-- The desired tables are all those in RS1 that are not in RS2.

INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName, FKReference)
SELECT OBJECT_NAME(RS1.fkeyid), @i, OBJECT_NAME(RS1.constid), OBJECT_NAME(RS1.rkeyid)
FROM    (SELECT fkeyid, constid, rkeyid
        FROM sysreferences WITH (NOLOCK)
        WHERE rkeyid = fkeyid ) RS1
        LEFT OUTER JOIN
        (SELECT DISTINCT fkeyid
        FROM sysreferences WITH (NOLOCK)
        WHERE fkeyid <> rkeyid ) RS2
        ON RS1.fkeyid = RS2.fkeyid
WHERE RS2.fkeyid IS NULL


-- Now, drill down in the FK hierarchy. Get all tables
-- that have a FK that references one or more tables in #tblFKTableOrder,
-- yet only references tables that are in #tblFKTableOrder(!), and that have not yet
-- been recorded in #tblFKTableOrder. Tables that reference themselves, as well
-- as tables in #tblFKTableOrder, are considered as well.
-- This is done in a loop, and the loop terminates when we reach the lowest level
-- in the hierarchy (i.e., when no more tables meet the listed condition).

WHILE @Cnt > 0
BEGIN
        -- Analyze the next level in the hierarchy.
        SET @i = @i + 1


        -- Get all tables that reference tables that are recorded
        -- in #tblFKTableOrder (can also reference themselves),
        -- and do not references tables that
        -- were not yet recorded.
        -- This is done by as follows:
        -- RS1 conatains the tables that have FK constraints
        -- that reference tables in #tblFKTableOrder (and possibly have
        -- a self-reference). RS2 contains all tables that reference tables
        -- that are not yet in #tblFKTableOrder (excluding self-refences).
        -- We write into #tblFKTableOrder the tables in RS1, which are
        -- not in RS2.

        INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName, FKReference)
        SELECT OBJECT_NAME(a.fkeyid), @i, OBJECT_NAME(a.constid),  OBJECT_NAME(a.rkeyid)
        FROM sysreferences a
                INNER JOIN
                (SELECT DISTINCT z.fkeyid
                FROM sysreferences z WITH (NOLOCK)
                        INNER JOIN #tblFKTableOrder y WITH (NOLOCK)
                        ON OBJECT_NAME(z.rkeyid) = y.TableName
                        LEFT OUTER JOIN #tblFKTableOrder v WITH (NOLOCK)
                        ON OBJECT_NAME(z.fkeyid) = v.TableName
                WHERE v.TableName IS NULL) RS1
                ON a.fkeyid = RS1.fkeyid
                LEFT OUTER JOIN
                (SELECT DISTINCT x.fkeyid
                FROM sysreferences x WITH (NOLOCK)
                        LEFT OUTER JOIN #tblFKTableOrder w WITH (NOLOCK)
                        ON OBJECT_NAME(x.rkeyid) = w.TableName
                WHERE x.fkeyid <> x.rkeyid
                        AND w.TableName IS NULL) RS2
                ON RS1.fkeyid = RS2.fkeyid
        WHERE RS2.fkeyid IS NULL


        SET @Cnt = @@ROWCOUNT
END

SET NOCOUNT OFF

SELECT * FROM #tblFKTableOrder
ORDER BY HierarchyLevel ASC, TableName ASC, FKName ASC
GO

--