Table Reference Levels

CREATE FUNCTION RISequence
(
)
RETURNS @RISEQUENCE TABLE
(
        TABLE_NAME SYSNAME,
        PRIORITY SMALLINT NOT NULL,
        PRIMARY KEY
        (
                TABLE_NAME))
AS
        --Returns The user tables and the "depth" of the table in the RI chain.
        --Tables with no FK's are given a PRIORITY of 0
        --Circular reference tables have a minimum PRIORITY of 1
BEGIN
        --COUNTER
        DECLARE @COUNTER  INT,
                @PRIORITY INT
                --RI Table Matching
        DECLARE @RIMATCH TABLE (PK_TABLE SYSNAME NOT NULL, FK_TABLE SYSNAME NOT NULL)
                --Populate @RIMATCH -- Remove Self referenced entities (WHERE expression)
                INSERT @RIMATCH
                        (
                                PK_TABLE,
                                FK_TABLE
                        )
                SELECT  PK.TABLE_SCHEMA + '.' + PK.TABLE_NAME          AS PK_TABLE ,
                        FK.TABLE_SCHEMA + '.' + FK.TABLE_NAME          AS FK_TABLE
                FROM    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS     AS RC
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FK
                        ON      FK.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PK
                        ON      PK.CONSTRAINT_NAME       = RC.UNIQUE_CONSTRAINT_NAME
                WHERE   FK.TABLE_SCHEMA + FK.TABLE_NAME != PK.TABLE_SCHEMA + PK.TABLE_NAME
                        --No FK's (PRIORITY=0)
                        SET @PRIORITY = 0
                INSERT @RISequence
                        (
                                TABLE_NAME,
                                PRIORITY
                        )
                SELECT  TABLE_SCHEMA + '.' + TABLE_NAME,
                        @PRIORITY
                FROM    INFORMATION_SCHEMA.TABLES AS T
                WHERE   TABLE_TYPE  = 'BASE TABLE'
                    AND TABLE_NAME != 'dtproperties'
                    AND NOT EXISTS
                        (SELECT 1
                        FROM    @RIMATCH C
                        WHERE   C.FK_TABLE = T.TABLE_SCHEMA + '.' + T.TABLE_NAME
                        )
                        --Circular Reference (CR) PRIORITY=1 ie The only FK is the circular FK
                        SET @PRIORITY = @PRIORITY + 1
                INSERT @RISequence
                        (
                                TABLE_NAME,
                                PRIORITY
                        )
                SELECT  S.FK_TABLE,
                        @PRIORITY
                FROM    @RIMATCH S
                WHERE   EXISTS
                        (SELECT 1
                        FROM    @RIMATCH X
                        WHERE   X.PK_TABLE = S.FK_TABLE
                            AND X.FK_TABLE = S.PK_TABLE
                        )
                    AND NOT EXISTS
                        (SELECT 1
                        FROM    @RISequence C
                        WHERE   C.TABLE_NAME = S.FK_TABLE
                        )
                GROUP BY S.FK_TABLE
                HAVING COUNT(         *) =
                        (SELECT COUNT(*)
                        FROM    @RIMATCH K
                        WHERE   S.FK_TABLE = K.FK_TABLE
                        )
                        --Fudge a DO operation
                        SET @COUNTER = 1 WHILE @Counter > 0 BEGIN
                        SET @COUNTER = 0
                --Standard RI
                INSERT @RISequence
                        (
                                TABLE_NAME,
                                PRIORITY
                        )
                SELECT  K.FK_TABLE ,
                        @PRIORITY
                FROM    @RIMATCH AS K
                        INNER JOIN @RISequence O
                        ON      O.TABLE_NAME = K.PK_TABLE
                WHERE   NOT EXISTS
                        (SELECT 1
                        FROM    @RISequence F
                        WHERE   K.FK_TABLE = F.TABLE_NAME
                        )
                GROUP BY K.FK_TABLE
                HAVING COUNT(         *) =
                        (SELECT COUNT(*)
                        FROM    @RIMATCH S
                        WHERE   S.FK_TABLE = K.FK_TABLE
                        )
                        SET @COUNTER =
                        CASE @@ROWCOUNT
                                WHEN 0
                                THEN 0
                                ELSE 1
                        END
                        --Because of the forced discovery of PRIORITY 1 CR, increment takes place here
                        SET @PRIORITY = @PRIORITY + 1
                --Circular References with related RI at the current PRIORITY Level
                INSERT @RISequence
                        (
                                TABLE_NAME,
                                PRIORITY
                        )
                SELECT  S.FK_TABLE,
                        @PRIORITY
                FROM    @RIMATCH S
                        INNER JOIN @RIMATCH X
                        ON      X.PK_TABLE = S.FK_TABLE
                            AND X.FK_TABLE = S.PK_TABLE
                WHERE   NOT EXISTS
                        (SELECT 1
                        FROM    @RISequence W
                        WHERE   S.FK_TABLE = W.TABLE_NAME
                        )
                    AND EXISTS
                        (SELECT 1
                        FROM    @RIMATCH Z
                        WHERE   S.PK_TABLE  = Z.FK_TABLE
                            AND Z.PK_TABLE != S.FK_TABLE
                            AND EXISTS
                                (SELECT 1
                                FROM    @RISequence A
                                WHERE   Z.PK_TABLE = A.TABLE_NAME
                                )
                        )
                        SET @COUNTER = @COUNTER +
                        CASE @@ROWCOUNT
                                WHEN 0
                                THEN 0
                                ELSE 1
                        END
        END RETURN
END GO




SELECT  *
FROM    dbo.RISequence()
ORDER BY PRIORITY,
        TABLE_NAME
        --DROP FUNCTION RISequence