Foreign key dependency tree in T-SQL

3.sql

;with RowPerForeignKeyColumn as (
select
	--fk.name, fk.is_system_named,
	fkChild.ChildTableName, fkChild.ChildColumnName, fkChild.ChildColumnOrdinal,
	fkParent.ParentTableName, fkParent.ParentColumnName, fkParent.ParentColumnOrdinal,
	ConstraintName = fk.name,
	fkChild.ChildSchema,
	fkParent.ParentSchema --,
	--fkParent.*, fkChild.*
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
inner join (
	select
		ChildSchema = s.name,
		ChildTableId = t.object_id,
		ChildTableName = t.name,
		ChildColumnOrdinal = c.column_id,
		ChildColumnName = c.name
	from sys.tables t
	inner join sys.schemas s on t.schema_id = s.schema_id
	inner join sys.columns c on c.object_id = t.object_id
) fkChild
	on fkChild.ChildTableId = fkc.parent_object_id
	and fkChild.ChildColumnOrdinal = fkc.parent_column_id
inner join (
	select
		ParentSchema = s.name,
		ParentTableId = t.object_id,
		ParentTableName = t.name,
		ParentColumnOrdinal = c.column_id,
		ParentColumnName = c.name
	from sys.tables t
	inner join sys.schemas s on t.schema_id = s.schema_id
	inner join sys.columns c on c.object_id = t.object_id
) fkParent
	on fkParent.ParentTableId = fkc.referenced_object_id
	and fkParent.ParentColumnOrdinal = fkc.referenced_column_id

inner join sys.schemas s on fk.schema_id = s.schema_id
--where s.name = 'template' --and fk.name like '%ReferentialChild%'
--order by fkChild.ChildTableName, fkChild.ChildColumnName
),
ForeignKeyTree
as
(
	-- Anchor query
	select
		RootTable = ParentTableName,
		ChildTableName, ChildColumnName, ChildColumnOrdinal,
		ParentTableName, ParentColumnName, ParentColumnOrdinal,
		ConstraintName,
		RecursionLevel = 1
	from RowPerForeignKeyColumn fk
	--where ParentTableName = 'Range'
	--where PkTable not in (select FkTable from ForeignKeys)

	union all

	-- Recursive query
	select
		fkt.RootTable,
		fk.ChildTableName, fk.ChildColumnName, fk.ChildColumnOrdinal,
		fk.ParentTableName, fk.ParentColumnName, fk.ParentColumnOrdinal,
		fk.ConstraintName,
		fkt.RecursionLevel + 1
	from RowPerForeignKeyColumn fk
	inner join ForeignKeyTree fkt on fkt.ChildTableName = fk.ParentTableName
	where fkt.RecursionLevel <= 20
)

select *
from ForeignKeyTree
order by RootTable, RecursionLevel desc, ChildTableName

--select * from RowPerForeignKeyColumn

Foreign key dependency tree in T-SQL.sql

/*
Quick script making use of some queries from the C# Entity Framework Reverse POCO generator.
*/


set nocount on
begin transaction

drop table if exists PersonBorrowsDvd
drop table if exists Dvd
drop table if exists Person
drop table if exists Gender
drop table if exists Sex

create table Sex (
	SexCode nchar(1) primary key,
	[Desc] nvarchar(200) not null
)

create table Gender (
	GenderCode nchar(1) primary key,
	[Desc] nvarchar(200) not null
)

create table Person (
	FirstName nvarchar(30),
	Surname nvarchar(30),
	BirthPlace nvarchar(30),
	Sex nchar(1) not null,
	Gender nchar(1) not null,

	constraint PK_Person
	primary key (FirstName, Surname, BirthPlace),

	constraint FK_Person_Is_Sex
	foreign key (Sex) references Sex(SexCode),

	constraint FK_Person_Is_Gender
	foreign key (Gender) references Gender (GenderCode)
)

create table Dvd (
	Title nvarchar(30),
	ReleaseYear int,

	constraint PK_Dvd
	primary key (Title, ReleaseYear)
)

create table PersonBorrowsDvd (
	FirstName1 nvarchar(30),
	Surname2 nvarchar(30),
	BirthPlace3 nvarchar(30),
	Title nvarchar(30),
	ReleaseYear int,
	BorrowDate date not null,

	constraint PK_PersonBorrowsDvd
	primary key (FirstName1, Surname2, BirthPlace3, Title, ReleaseYear, BorrowDate),

	constraint FK_PersonBorrowsDvd_Person
	foreign key (FirstName1, Surname2, BirthPlace3)
	references Person (FirstName, Surname, BirthPlace),

	constraint FK_PersonBorrowsDvd_Dvd
	foreign key (Title, ReleaseYear)
	references Dvd (Title, ReleaseYear)
)



IF OBJECT_ID('tempdb..#Columns')     IS NOT NULL DROP TABLE #Columns;
IF OBJECT_ID('tempdb..#PrimaryKeys') IS NOT NULL DROP TABLE #PrimaryKeys;
IF OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL DROP TABLE #ForeignKeys;
IF OBJECT_ID('tempdb..#MyForeignKeys') IS NOT NULL DROP TABLE #MyForeignKeys;

SELECT  C.TABLE_SCHEMA,
        C.TABLE_NAME,
        C.COLUMN_NAME,
        C.ORDINAL_POSITION,
        C.COLUMN_DEFAULT,
        C.IS_NULLABLE,
        C.DATA_TYPE,
        C.CHARACTER_MAXIMUM_LENGTH,
        C.NUMERIC_PRECISION,
        C.NUMERIC_SCALE,
        C.DATETIME_PRECISION
INTO    #Columns
FROM    INFORMATION_SCHEMA.COLUMNS C
WHERE   C.TABLE_NAME NOT IN ('EdmMetadata', '__MigrationHistory')
        AND C.TABLE_NAME NOT LIKE 'sysdiagram%';

CREATE NONCLUSTERED INDEX IX_EfPoco_Columns
ON dbo.#Columns (TABLE_NAME)
INCLUDE (TABLE_SCHEMA,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION);

SELECT  u.TABLE_SCHEMA,
        u.TABLE_NAME,
        u.COLUMN_NAME,
        u.ORDINAL_POSITION
INTO    #PrimaryKeys
FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
            ON u.TABLE_SCHEMA COLLATE DATABASE_DEFAULT = tc.CONSTRAINT_SCHEMA COLLATE DATABASE_DEFAULT
               AND u.TABLE_NAME = tc.TABLE_NAME
               AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE   CONSTRAINT_TYPE = 'PRIMARY KEY';

SELECT DISTINCT
        u.TABLE_SCHEMA,
        u.TABLE_NAME,
        u.COLUMN_NAME
INTO    #ForeignKeys
FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
            ON u.TABLE_SCHEMA COLLATE DATABASE_DEFAULT = tc.CONSTRAINT_SCHEMA COLLATE DATABASE_DEFAULT
               AND u.TABLE_NAME = tc.TABLE_NAME
               AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY';





SELECT  c.TABLE_SCHEMA AS SchemaName,
        c.TABLE_NAME AS TableName,
        t.TABLE_TYPE AS TableType,
        c.ORDINAL_POSITION AS Ordinal,
        c.COLUMN_NAME AS ColumnName,
        CAST(CASE WHEN IS_NULLABLE = 'YES' THEN 1
                  ELSE 0
             END AS BIT) AS IsNullable,
        DATA_TYPE AS TypeName,
        ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) AS MaxLength,
        CAST(ISNULL(NUMERIC_PRECISION, 0) AS INT) AS Precision,
        ISNULL(COLUMN_DEFAULT, '') AS [Default],
        CAST(ISNULL(DATETIME_PRECISION, 0) AS INT) AS DateTimePrecision,
        ISNULL(NUMERIC_SCALE, 0) AS Scale,
        CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS IsIdentity,
        CAST(CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') = 1 THEN 1
                  WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') = 1 THEN 1
                  WHEN DATA_TYPE = 'TIMESTAMP' THEN 1
                  WHEN DATA_TYPE = 'UNIQUEIDENTIFIER'
                       AND LOWER(ISNULL(COLUMN_DEFAULT, '')) LIKE '%newsequentialid%' THEN 1
                  ELSE 0
             END AS BIT) AS IsStoreGenerated,
        CAST(CASE WHEN pk.ORDINAL_POSITION IS NULL THEN 0
                  ELSE 1
             END AS BIT) AS PrimaryKey,
        ISNULL(pk.ORDINAL_POSITION, 0) PrimaryKeyOrdinal,
        CAST(CASE WHEN fk.COLUMN_NAME IS NULL THEN 0
                  ELSE 1
             END AS BIT) AS IsForeignKey
INTO #MyPrimaryKeys
FROM    #Columns c
        LEFT OUTER JOIN #PrimaryKeys pk
            ON c.TABLE_SCHEMA = pk.TABLE_SCHEMA
               AND c.TABLE_NAME = pk.TABLE_NAME
               AND c.COLUMN_NAME = pk.COLUMN_NAME
        LEFT OUTER JOIN #ForeignKeys fk
            ON c.TABLE_SCHEMA = fk.TABLE_SCHEMA
               AND c.TABLE_NAME = fk.TABLE_NAME
               AND c.COLUMN_NAME = fk.COLUMN_NAME
        INNER JOIN INFORMATION_SCHEMA.TABLES t
            ON c.TABLE_SCHEMA COLLATE DATABASE_DEFAULT = t.TABLE_SCHEMA COLLATE DATABASE_DEFAULT
               AND c.TABLE_NAME COLLATE DATABASE_DEFAULT = t.TABLE_NAME COLLATE DATABASE_DEFAULT
WHERE   c.TABLE_NAME NOT IN ('EdmMetadata', '__MigrationHistory')
        AND c.TABLE_NAME NOT LIKE 'sysdiagram%'






SELECT  FK.name AS FkTable,
        FkCol.name AS FkColumn,
        PK.name AS PkTable,
        PkCol.name AS PkColumn,
        OBJECT_NAME(f.object_id) AS Constraint_Name,
        SCHEMA_NAME(FK.schema_id) AS fkSchema,
        SCHEMA_NAME(PK.schema_id) AS pkSchema,
        PkCol.name AS primarykey,
        k.constraint_column_id AS ORDINAL_POSITION,
        CASE WHEN f.delete_referential_action = 1 THEN 1 ELSE 0 END as CascadeOnDelete,
        f.is_disabled AS IsNotEnforced
INTO #MyForeignKeys

FROM    sys.objects AS PK
        INNER JOIN sys.foreign_keys AS f
            INNER JOIN sys.foreign_key_columns AS k
                ON k.constraint_object_id = f.object_id
            INNER JOIN sys.indexes AS i
                ON f.referenced_object_id = i.object_id
                   AND f.key_index_id = i.index_id
            ON PK.object_id = f.referenced_object_id
        INNER JOIN sys.objects AS FK
            ON f.parent_object_id = FK.object_id
        INNER JOIN sys.columns AS PkCol
            ON f.referenced_object_id = PkCol.object_id
               AND k.referenced_column_id = PkCol.column_id
        INNER JOIN sys.columns AS FkCol
            ON f.parent_object_id = FkCol.object_id
               AND k.parent_column_id = FkCol.column_id
ORDER BY FkTable, FkColumn

--select * from #MyForeignKeys

go



declare @TableToDrop sysname = 'Gender';
;with ForeignKeyTree (FkTable, FkColumn, PkTable, PkColumn, RecursionLevel, RootTable)
as
(
	-- Anchor query
	select FkTable, FkColumn, PkTable, PkColumn, RecursionLevel = 0, RootTable = @TableToDrop
	from #MyForeignKeys fk
	where PkTable = @TableToDrop
	--where PkTable not in (select FkTable from ForeignKeys)

	union all

	-- Recursive query
	select fk.FkTable, fk.FkColumn, fk.PkTable, fk.PkColumn, fkt.RecursionLevel + 1, fkt.RootTable
	from #MyForeignKeys fk
	inner join ForeignKeyTree fkt on fkt.FkTable = fk.PkTable
	where fkt.RecursionLevel <= 20
)

select *
from ForeignKeyTree
order by RecursionLevel desc, FkTable


rollback transaction

Minor cleanup.sql

set nocount on

begin transaction

IF OBJECT_ID('tempdb..#Columns')     IS NOT NULL DROP TABLE #Columns;
IF OBJECT_ID('tempdb..#PrimaryKeys') IS NOT NULL DROP TABLE #PrimaryKeys;
IF OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL DROP TABLE #ForeignKeys;
IF OBJECT_ID('tempdb..#MyForeignKeys') IS NOT NULL DROP TABLE #MyForeignKeys;


IF OBJECT_ID('dbo.TableA') IS NOT NULL DROP TABLE TableA;
create table TableA (
	TableA_ColA int,
	TableA_ColB int,
	TableA_ColC int,
	primary key (TableA_ColA, TableA_ColB, TableA_ColC)
)

IF OBJECT_ID('dbo.TableB') IS NOT NULL DROP TABLE TableB;
create table TableB (
	TableB_ColA int,
	TableB_ColB int,
	TableB_ColC int,
	primary key (TableB_ColA, TableB_ColB, TableB_ColC),

	foreign key (TableB_ColA, TableB_ColB, TableB_ColC)
	references TableA (TableA_ColA, TableA_ColB, TableA_ColC)
)

IF OBJECT_ID('dbo.TableC') IS NOT NULL DROP TABLE TableC;
create table TableC (
	TableC_ColA int,
	TableC_ColB int,
	TableC_ColC int,
	primary key (TableC_ColA, TableC_ColB, TableC_ColC),

	foreign key (TableC_ColA, TableC_ColB, TableC_ColC)
	references TableB (TableB_ColA, TableB_ColB, TableB_ColC)
)


SELECT  C.TABLE_SCHEMA,
        C.TABLE_NAME,
        C.COLUMN_NAME,
        C.ORDINAL_POSITION,
        C.COLUMN_DEFAULT,
        C.IS_NULLABLE,
        C.DATA_TYPE,
        C.CHARACTER_MAXIMUM_LENGTH,
        C.NUMERIC_PRECISION,
        C.NUMERIC_SCALE,
        C.DATETIME_PRECISION
INTO    #Columns
FROM    INFORMATION_SCHEMA.COLUMNS C
WHERE   C.TABLE_NAME NOT IN ('EdmMetadata', '__MigrationHistory')
        AND C.TABLE_NAME NOT LIKE 'sysdiagram%';

CREATE NONCLUSTERED INDEX IX_EfPoco_Columns
ON dbo.#Columns (TABLE_NAME)
INCLUDE (TABLE_SCHEMA,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION);





SELECT  FK.name AS FkTable,
        FkCol.name AS FkColumn,
        PK.name AS PkTable,
        PkCol.name AS PkColumn,
        OBJECT_NAME(f.object_id) AS Constraint_Name,
        SCHEMA_NAME(FK.schema_id) AS fkSchema,
        SCHEMA_NAME(PK.schema_id) AS pkSchema,
        PkCol.name AS primarykey,
        k.constraint_column_id AS ORDINAL_POSITION,
        CASE WHEN f.delete_referential_action = 1 THEN 1 ELSE 0 END as CascadeOnDelete,
        f.is_disabled AS IsNotEnforced
INTO #MyForeignKeys

FROM    sys.objects AS PK
        INNER JOIN sys.foreign_keys AS f
            INNER JOIN sys.foreign_key_columns AS k
                ON k.constraint_object_id = f.object_id
            INNER JOIN sys.indexes AS i
                ON f.referenced_object_id = i.object_id
                   AND f.key_index_id = i.index_id
            ON PK.object_id = f.referenced_object_id
        INNER JOIN sys.objects AS FK
            ON f.parent_object_id = FK.object_id
        INNER JOIN sys.columns AS PkCol
            ON f.referenced_object_id = PkCol.object_id
               AND k.referenced_column_id = PkCol.column_id
        INNER JOIN sys.columns AS FkCol
            ON f.parent_object_id = FkCol.object_id
               AND k.parent_column_id = FkCol.column_id
ORDER BY FkTable, FkColumn






--select * from #MyForeignKeys





----------------------------------------------------------------------------------------------------

declare @TableToDrop sysname = 'TableA';
;with ForeignKeyTree (FkTable, FkColumn, PkTable, PkColumn, RecursionLevel, RootTable)
as
(
	-- Anchor query
	select FkTable, FkColumn, PkTable, PkColumn, RecursionLevel = 1, RootTable = @TableToDrop
	from #MyForeignKeys fk
	where PkTable = @TableToDrop or @TableToDrop = ''
	union
	select '', '', @TableToDrop, '', Recursionlevel = 0, RootTable = @TableToDrop
	where @TableToDrop <> ''
	--where PkTable not in (select FkTable from ForeignKeys)

	union all

	-- Recursive query
	select fk.FkTable, fk.FkColumn, fk.PkTable, fk.PkColumn, fkt.RecursionLevel + 1, fkt.RootTable
	from #MyForeignKeys fk
	inner join ForeignKeyTree fkt on fkt.FkTable = fk.PkTable
	where fkt.RecursionLevel <= 20
)

select *
from ForeignKeyTree
order by RecursionLevel desc, FkTable


rollback transaction