SQL- Identify table dependencies

Hi!,

Many a times we get into the problem of identifying the dependency of the given table, normally what we do is open SQL Server Object Explorer, select database, select a particular table and then right click on it and then select View dependencies option. This works fine when we deal with individual table.

But what If I need to know the dependency of multiple tables? Obviously we can’t go and do the above said steps to all the tables.

So, to get rid of this I prepared the SQL query which lists all tables available in a database along with dependency level. You can tailor this  to filter for a particular list of tables.

The DependencyLevel column contains ZERO, then those tables are independent.

Here is the query:

WITH fk_tables AS

(

    SELECT

            from_schema = s1.name

            ,from_table = o1.Name

            ,to_schema  = s2.name

            ,to_table   = o2.Name

    FROM

            sys.foreign_keys fk   

            INNER JOIN sys.objects o1 ON ( fk.parent_object_id = o1.object_id )  

            INNER JOIN sys.schemas s1 ON ( o1.schema_id = s1.schema_id )   

            INNER JOIN sys.objects o2 ON ( fk.referenced_object_id = o2.object_id )   

            INNER JOIN sys.schemas s2 ON ( o2.schema_id = s2.schema_id )

    /*For the purposes of finding dependency hierarchy      

        we're not worried about self-referencing tables*/

    WHERE

            NOT( s1.name = s2.name AND o1.name = o2.name)

)

,

ordered_tables ( schemaName, tableName, DependencyLevel )

 AS

(        SELECT   

                  s.name

                  ,t.name

                  ,0   

        FROM   

                  ( SELECT * FROM sys.tables WHERE name <> 'sysdiagrams') t   

                  INNER JOIN sys.schemas s ON ( t.schema_id = s.schema_id )

                  LEFT OUTER JOIN fk_tables fk ON ( s.name = fk.from_schema AND t.name = fk.from_table )   

        WHERE

                  fk.from_schema IS NULL

                 

        UNION ALL

       

        SELECT   

                  fk.from_schema

                  ,fk.from_table

                  ,ot.DependencyLevel + 1   

        FROM

                  fk_tables fk   

                  INNER JOIN ordered_tables ot ON ( fk.to_schema = ot.schemaName AND fk.to_table = ot.tableName )

)

SELECT DISTINCT

      TableName = '[' + ot.schemaName + '].[' +  ot.tableName + ']'

      , ot.DependencyLevel

FROM

      ordered_tables ot

      INNER JOIN (

                              SELECT

                                    schemaName

                                    ,tableName

                                    ,MAX(DependencyLevel) maxLevel       

                              FROM

                                    ordered_tables       

                              GROUP BY

                                    schemaName,tableName

                        ) mx ON ( ot.schemaName = mx.schemaName

                                      AND ot.tableName = mx.tableName

                                      AND mx.maxLevel = ot.DependencyLevel )

WHERE

      ot.schemaName = 'dbo'

ORDER BY

       ot.DependencyLevel ASC