Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Script to detect table subsets based on foreign key dependencies
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.