SQL Server Foreign Key Hierarchy Order and Dependency List Script

By:   |   Updated: 2019-09-24   |   Comments (3)   |   Related: More > Database Design

Problem

Sometimes you must partially copy SQL Server objects from a source to another destination, and if you don't know the object dependency for foreign keys, you can end with some errors of this type:

Msg 547, Level 16, State 0, Line 6
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_orders". The conflict occurred in database "YourDB", table "dbo.ORDERS", column 'OrderID'
.

If it is just one table, you can use SQL Server Management Studio (SSMS) or any other tool to visually check dependencies and correct your script. But what happens when you have multiple tables? The manual process of getting the correct dependencies and ordering the tables and then generating the DML commands in the correct order can be very difficult.

In this tip we will show you a script that you can adapt to your needs that can help you to generate your DML commands more easily by showing the ordering of objects based on foreign key constraints.

Solution

With the help of the WITH command, we can create a common table expression and using its recursive functionality to get a listing of objects and their order of referencing other objects.

Here is the complete script, but we will walk through the sections to give you an idea how this works.

WITH dependencies -- Get object with FK dependencies
AS (
    SELECT FK.TABLE_NAME AS Obj
        , PK.TABLE_NAME AS Depends
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    ), 
no_dependencies -- The first level are objects with no dependencies 
AS (
    SELECT 
        name AS Obj
    FROM sys.objects
    WHERE name NOT IN (SELECT obj FROM dependencies) --we remove objects with dependencies from first CTE
    AND type = 'U' -- Just tables
    ), 
recursiv -- recursive CTE to get dependencies
AS (
    SELECT Obj AS [Table]
        , CAST('' AS VARCHAR(max)) AS DependsON
        , 0 AS LVL -- Level 0 indicate tables with no dependencies
    FROM no_dependencies
 
    UNION ALL
 
    SELECT d.Obj AS [Table]
        , CAST(IIF(LVL > 0, r.DependsON + ' > ', '') + d.Depends AS VARCHAR(max)) -- visually reflects hierarchy
        , R.lvl + 1 AS LVL
    FROM dependencies d
    INNER JOIN recursiv r
        ON d.Depends = r.[Table]
    )
-- The final result, with some extra fields for more information
SELECT DISTINCT SCHEMA_NAME(O.schema_id) AS [TableSchema]
    , R.[Table]
    , R.DependsON
    , R.LVL
FROM recursiv R
INNER JOIN sys.objects O
    ON R.[Table] = O.name
ORDER BY R.LVL
    , R.[Table]

The script is divided into 4 parts, as follows:

1 - Dependencies

SELECT FK.TABLE_NAME AS Obj 
, PK.TABLE_NAME AS Depends 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 

With the help of the system functions INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS and INFORMATION_SCHEMA.TABLE_CONSTRAINTS we can join them and obtain the objects that have any foreign key references. A sample output is as follows:

image 1: object dependency list

2 - No Dependencies

SELECT 
    name AS Obj 
FROM sys.objects 
WHERE name NOT IN (SELECT obj FROM dependencies) --we remove objects with dependencies from first CTE 
  AND type = 'U' -- Just tables 

These are the objects with no dependencies, this is the first level in our hierarchy (level 0), to obtain these objects, we exclude them from the total of tables, the ones we obtained in the first query.  Also, for this script we filter the objects to obtain only tables.

3 - Recursiv Logic

SELECT Obj AS [Table] 
        , CAST('' AS VARCHAR(max)) AS DependsON 
        , 0 AS LVL 
FROM no_dependencies 
  
UNION ALL 
  
SELECT d.Obj AS [Table] 
        , CAST(IIF(LVL > 0, r.DependsON + ' > ', '') + d.Depends AS VARCHAR(max)) -- visually reflects hierarchy 
        , R.lvl + 1 AS LVL 
FROM dependencies d 
INNER JOIN recursiv r 
   ON d.Depends = r.[Table] 

The recursive part of the query, we take as a first level (level 0) the objects without dependencies, and then a UNION ALL where the recursive part is joined with the first part (the objects with dependencies). You can learn more about how recursive CTEs work in this tip.

A small string manipulation is included in the recursive query to be able to visually represent the dependency of the object, especially helpful when an object has several dependencies.

4 - Outer Query

-- The final result, with some extra fields for more information
-- The final result, with some extra fields for more information 
SELECT DISTINCT SCHEMA_NAME(O.schema_id) AS [TableSchema] 
    , R.[Table] 
    , R.DependsON 
    , R.LVL 
FROM recursiv R 
INNER JOIN sys.objects O 
    ON R.[Table] = O.name 
ORDER BY R.LVL 
    , R.[Table] 

In this last section, we call the recursive final results and prepare it to be presented to the user, we order the results by level and then by table name (you can also order by schema if you want, just by changing/adding the [TableSchema] to the ORDER BY clause.

Query Results

If we execute the overall query, we will have a result like this (I have executed it against the AdventureWorks2017 database):

image 2: script execution, first level

You can see that the first level does not have any dependencies, but if you scroll down a little bit to the next level you will start seeing object dependencies:

image 3: script execution, dependencies

And if you scroll until the end, you will see the full dependency path for the tables at the highest level:

image 4: script execution, full dependency path

Why are there objects that appears at multiple levels?

Note that some of the objects appear in various levels (for example SalesOrderHeader), this is because it has different dependencies at different levels.

If you want to show each object just once at the highest level, you must change the part four (outer query) of the script to use a GROUP BY clause and remove the dependency path field [DependsON], like this:

SELECT SCHEMA_NAME(O.schema_id) AS [TableSchema]
    , R.[Table]
    , max(R.LVL) as LVL
FROM recursiv R
INNER JOIN sys.objects O
    ON R.[Table] = O.name
GROUP BY O.schema_id, R.[Table]
ORDER BY LVL
    , R.[Table]

Here is the complete set of code.

WITH dependencies -- Get object with FK dependencies
AS (
    SELECT FK.TABLE_NAME AS Obj
        , PK.TABLE_NAME AS Depends
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    ), 
no_dependencies -- The first level are objects with no dependencies 
AS (
    SELECT 
        name AS Obj
    FROM sys.objects
    WHERE name NOT IN (SELECT obj FROM dependencies) --we remove objects with dependencies from first CTE
    AND type = 'U' -- Just tables
    ), 
recursiv -- recursive CTE to get dependencies
AS (
    SELECT Obj AS [Table]
        , CAST('' AS VARCHAR(max)) AS DependsON
        , 0 AS LVL -- Level 0 indicate tables with no dependencies
    FROM no_dependencies
 
    UNION ALL
 
    SELECT d.Obj AS [Table]
        , CAST(IIF(LVL > 0, r.DependsON + ' > ', '') + d.Depends AS VARCHAR(max)) -- visually reflects hierarchy
        , R.lvl + 1 AS LVL
    FROM dependencies d
    INNER JOIN recursiv r
        ON d.Depends = r.[Table]
    )
-- The final result with each table only shown once
SELECT SCHEMA_NAME(O.schema_id) AS [TableSchema]
    , R.[Table]
    , max(R.LVL) as LVL
FROM recursiv R
INNER JOIN sys.objects O
    ON R.[Table] = O.name
GROUP BY O.schema_id, R.[Table]
ORDER BY LVL
    , R.[Table]

Here is the output:

image 5: script execution after modification

We can now see the example object [SalesOrderHeader] just appears one time at the highest dependency level.

Now you can select what option suits best your needs and apply it to your processes.

Next Steps
  • Please note that this script does not support self-referencing foreign keys, the script will fail if one exists on your database, since a recursion infinite loop is created.
  • You can check the Microsoft official documentation about Common Table Expressions here, there is a section about recursive CTE and their considerations.
  • You can check this tip about recursive CTE.


Last Updated: 2019-09-24


get scripts

next tip button



About the author
MSSQLTips author Eduardo Pivaral Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, September 24, 2019 - 12:32:23 PM - Dave Boltman Back To Top

Hi Eduardo, yes,

> In my database I have two tables that refer to each other, and so this script gives the following error:

It is a common scenario, e.g. A --FK to--> B and also B --FK to--A. It's not enough to check just for self-referencing objects, but objects that reference any others in the recursive chain so far. I managed to get it right previously for a similar query for dependencies (of views I think, it was long ago).

I'll also try and work on it if I get a chance...


Tuesday, September 24, 2019 - 9:38:05 AM - Eduardo Pivaral Back To Top

Hi Dave,

Unless you have objects with over 100 levels of dependency, it seems the issue is with a self-referencing object, this cause the recursive query to enter in an infinite loop that is stopped by SQL Server with the MAXRECURSION parameter, and that is the error you are encountering.

I will work on an update to check self-referencing objects and indicate those on the results!


Tuesday, September 24, 2019 - 4:44:30 AM - Dave Boltman Back To Top

Very useful! Thank you.

In my database I have two tables that refer to each other, and so this script gives the following error:

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

It would be simple to check and prevent that. I'll try if I get a chance to add the check to your script.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools