Script to detect table subsets based on foreign key dependencies

detect table subsets.sql

DROP TABLE IF EXISTS #Tree;
CREATE TABLE #Tree
(
	object_id INT PRIMARY KEY WITH(IGNORE_DUP_KEY=ON),
	subset_group_id INT,
	referenced_object_id INT NULL
);

-- Insert 1st level tables
INSERT INTO #Tree
SELECT object_id, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), NULL
FROM sys.tables
WHERE object_id NOT IN
(
SELECT parent_object_id
FROM sys.foreign_keys
WHERE parent_object_id <> referenced_object_id
)

-- Insert recursive members based on foreign keys
WHILE @@ROWCOUNT > 0
BEGIN
	INSERT INTO #Tree
	SELECT DISTINCT fk.parent_object_id, t.subset_group_id, fk.referenced_object_id
	FROM sys.foreign_keys AS fk
	INNER JOIN #Tree AS t
	ON fk.referenced_object_id = t.object_id
	WHERE fk.parent_object_id <> fk.referenced_object_id
	AND fk.parent_object_id NOT IN (SELECT object_id FROM #Tree);
END

-- Output results
SELECT
	OBJECT_SCHEMA_NAME(object_id) AS schemaName,
	OBJECT_NAME(object_id) AS tableName,
	subset_group_id,
	COUNT(subset_group_id) OVER (PARTITION BY subset_group_id) AS tables_in_subset
FROM #Tree
ORDER BY subset_group_id, 1, 2