By: Eduardo Pivaral | Updated: 2019-09-24 | Comments (4) | Related: > 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:
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:
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):
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:
And if you scroll until the end, you will see the full dependency path for the tables at the highest level:
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:
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.
About the author
Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-09-24
