If you need to delete and then insert data into tables which have parent-child relationships defined via primary and foreign keys then the delete and insert commands have to be executed in proper sequence.
/*
DESCRIPTION:
- This script will generate a list of tables along with their
parent-child dependency heirarchy
- It can be used for documentation and data dictionary
- It can help in creating DELETE and INSERT scripts in
correct sequence where there are PK/FK relationships
KNOWN ISSUES:
It does not handle circular references in relationships.
It takes 30 seconds or more if run on a database with hundreds of tables
For a faster script, look at Erland Sommarskog's response in this forum thread -
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e988b95a-d45e-4d15-9f2e-3e4b1d7fa0d9/#8fe0b9b7-e430-471b-81da-2a55d03b06e0
CHANGE HISTORY:
2013/Jan/26 - Version 1
INPUT PARAMETERS:
None
COMPATIBILITY:
SQL Server version 2008 and later due to use of sys.sql_expression_dependencies
HOW TO USE:
Select the database and execute the script.
Use ORDER BY LevelNumber ASC for INSERT sequence and DESC for DELETE sequence.
SOURCE:
http://aalamrangi.wordpress.com/2013/02/21/parent-child-table-dependency-script/
*/
WITH
CteForData
AS (
SELECT s.name AS SchemaName,
o.name AS TableName,
/* self referencing tables have same parent
and child table names. Make it NULL */
NULLIF(OBJECT_NAME(rkeyid), o.name) AS ParentTable,
QUOTENAME(OBJECT_NAME(fkeyid))
+ '.'
+ (SELECT QUOTENAME(c.name)
FROM sys.syscolumns AS c
WHERE c.id = fkeyid
AND c.colid = fkey)
+ ' = '
+ QUOTENAME(OBJECT_NAME(rkeyid))
+ '.'
+ (SELECT QUOTENAME(c.name)
FROM sys.syscolumns AS c
WHERE c.id = rkeyid
AND c.colid = rkey) AS LinkingInformation
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
LEFT JOIN sys.sysforeignkeys AS f
ON o.object_id = f.fkeyid
WHERE o.type = 'U'
),
CteForHierarchy
AS (
SELECT 1 AS LevelNumber,
m.SchemaName,
m.TableName,
m.ParentTable,
m.LinkingInformation
FROM CteForData AS m
WHERE m.ParentTable IS NULL
UNION ALL
SELECT a.LevelNumber + 1 AS LevelNumber,
b.SchemaName,
b.TableName,
b.ParentTable,
b.LinkingInformation
FROM CteForHierarchy AS a
INNER JOIN CteForData AS b
ON a.TableName = b.ParentTable
)
SELECT DISTINCT
c.LevelNumber,
c.SchemaName,
c.TableName,
c.ParentTable,
c.LinkingInformation,
'DELETE FROM '
+ QUOTENAME(c.SchemaName)
+ '.'
+ QUOTENAME(c.TableName) AS DeleteCommand
FROM CteForHierarchy AS c
/* Just show the maximum level number */
INNER JOIN (SELECT SchemaName,
TableName,
MAX(LevelNumber) AS LevelNumber
FROM CteForHierarchy
GROUP BY SchemaName,
TableName) AS m
ON c.SchemaName = m.SchemaName
AND c.TableName = m.TableName
AND c.LevelNumber = m.LevelNumber
ORDER BY c.LevelNumber, -- ASC for INSERT sequence and DESC for DELETE sequence.
c.SchemaName,
c.TableName,
c.ParentTable