Access SQL objects using the right order

Sometimes you may want to obtain the correct order to access tables.

You may want, for example, to create a script that empty a group af tables following the order imposed by their foreign key constraints.

In SQL Server, relations between tables are available using sys.foreign_keys system view (parent_object_id, referenced_object_id columns).

Relations between procedures, views and functions are available using sys.sql_dependencies (object_id, referenced_major_id) and mantained by SQL Server using object_id values (SQL Server 2008 provides a new approach).

If you create a table T1 and then a procedure P1 that refers to T1 you'll find in the system view something like:

P1 (object_id), T1 (referenced_major_id).

If you drop and re-create T1 you'll loose this relation until ALTER P1.

The following script provides object depth. It not deals with circular references.

DECLARE @DEPTH INT

DECLARE @COUNT INT

IF OBJECT_ID('tempdb..#OBJ') IS NOT NULL

    DROP TABLE #OBJ

SELECT

    O.object_id,

    0 depth

INTO #OBJ

FROM

    sys.objects O

WHERE

    NOT EXISTS (SELECT TOP 1 1 FROM sys.extended_properties EP WHERE EP.major_id = O.object_id AND EP.name = 'microsoft_database_tools_support') AND

    O.is_ms_shipped = 0 AND

    O.type IN

    (

        'U', --tables

        'P', --procedures

        'V', --views

        'TR', --triggers

        'SN', --synonyms

        'FN','IF','TF' --functions

    )

SET @COUNT = @@ROWCOUNT

SET @DEPTH = 0

WHILE @COUNT > 0 AND @DEPTH < 25 --stop circular references

BEGIN

    SET @DEPTH = @DEPTH + 1

    UPDATE T1 SET

        DEPTH = @DEPTH

    FROM

        #OBJ T1

            INNER JOIN

        (

            SELECT parent_object_id, referenced_object_id FROM sys.foreign_keys

            UNION

            SELECT object_id, referenced_major_id FROM sys.sql_dependencies

            UNION

            SELECT object_id, parent_id FROM sys.triggers

        ) F ON

            F.parent_object_id = T1.object_id

            INNER JOIN

        #OBJ T2 ON

            T2.object_id = F.referenced_object_id

    WHERE

        T1.object_id <> T2.object_id AND --avoid self references

        T2.depth = @DEPTH-1

    SET @COUNT = @@ROWCOUNT

END

SELECT

    T.depth,

    O.type,

    T.object_id,

    SCHEMA_NAME(O.schema_id) + '.' + O.name obj_name

FROM

    #OBJ T

        INNER JOIN

    sys.objects O ON

        O.object_id = T.object_id

 ORDER BY

    T.DEPTH DESC

You can download the full script.

ObjectDepth.zip