I'm working a a recursive CTE to trace all relationships in a DB, using foreign keys, in order to create some test data. Everything is working well, up until the point that I hit a table that contains many-to-many relationships. I've taken a look at some of the other answers out there related to many-to-many tables, but it's just not clicking with me - can someone take a look at what I have and help point me in the right direction? Specifically what is happening is that I'm reaching a table that is a Budget/Client table (id, budgetid, clientid) from the Client side. At that point the recursion stops; I would want/expect it to traverse to the budget table (via budgetid) and continue to any related tables after that.
|Users | |Client | |BudgetClient | |Budget |
|userid (pk)|==\ |clientid (pk)|==\ |id (pk) | /==|budgetid (pk)|
\==|userid (fk) | \ |budgetid (fk)|==/
\==|clientid (fk)|
Existing code (current attempt) is below:
WITH cte AS
(
SELECT DISTINCT fk.object_id, fk.schema_id, fk.parent_object_id, fc.parent_column_id, t.schema_id AS referenced_schema_id, fk.referenced_object_id, ic.column_id AS referenced_column_id, 1 AS [Level]
FROM sys.foreign_keys fk
INNER JOIN sys.tables t ON fk.referenced_object_id = t.object_id
INNER JOIN sys.foreign_key_columns fc ON fk.object_id = fc.constraint_object_id
INNER JOIN sys.indexes i ON fk.referenced_object_id = i.object_id AND i.is_primary_key = 1
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE fk.type = 'F'
AND fk.referenced_object_id = OBJECT_ID(N'dbo.Users', N'U')
UNION ALL
SELECT fk.object_id, fk.schema_id, fk.parent_object_id, fc.parent_column_id, t.schema_id, fk.referenced_object_id, ic.column_id AS referenced_column_id, cte.[Level] + 1
FROM sys.foreign_keys fk
INNER JOIN sys.tables t ON fk.referenced_object_id = t.object_id
INNER JOIN sys.foreign_key_columns fc ON fk.object_id = fc.constraint_object_id
INNER JOIN sys.indexes i ON fk.referenced_object_id = i.object_id AND i.is_primary_key = 1
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN cte ON fk.referenced_object_id = cte.parent_object_id
WHERE fk.type = 'F'
AND fk.referenced_object_id <> cte.referenced_object_id
),
cteHierarchy AS (
SELECT DISTINCT
OBJECT_NAME(cte.object_id) AS ReferringKey,
SCHEMA_NAME(cte.schema_id) AS ReferringSchema,
OBJECT_NAME(cte.parent_object_id) as ReferringTable,
cte.parent_object_id AS reftableid,
COL_NAME(cte.parent_object_id,cte.parent_column_id) ReferringColumn,
SCHEMA_NAME(cte.referenced_schema_id) AS ReferencedSchema,
OBJECT_NAME(cte.referenced_object_id) as ReferencedTable,
COL_NAME(cte.referenced_object_id,cte.referenced_column_id) ReferencedColumn,
[Level]
FROM cte
)
SELECT ReferringKey, ReferringSchema, ReferringTable, ReferringColumn, ReferencedSchema, ReferencedTable, ReferencedColumn, [Level]
FROM cteHierarchy
ORDER BY [Level], ReferencedSchema, ReferencedTable, ReferringTable;
Sample data is below. DML is not necessary for this question, but provided to be thorough.
CREATE TABLE Users
(
userid INT NOT NULL,
CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (userid)
);
CREATE TABLE Client
(
clientid INT NOT NULL,
userid INT NOT NULL,
CONSTRAINT PK_Client PRIMARY KEY CLUSTERED (clientid),
CONSTRAINT FK_Client_Users FOREIGN KEY (userid) REFERENCES Users (userid)
);
CREATE TABLE Budget
(
budgetid INT NOT NULL,
CONSTRAINT PK_Budget PRIMARY KEY CLUSTERED (budgetid)
);
CREATE TABLE BudgetClient
(
id INT NOT NULL IDENTITY(1,1),
budgetid INT NOT NULL,
clientid INT NOT NULL,
CONSTRAINT PK_BudgetClient PRIMARY KEY CLUSTERED (id),
CONSTRAINT FK_BudgetClient_Budget FOREIGN KEY (budgetid) REFERENCES Budget (budgetid),
CONSTRAINT FK_BudgetClient_Client FOREIGN KEY (clientid) REFERENCES Client (clientid)
);
CREATE TABLE Company
(
companyid INT NOT NULL,
userid INT NOT NULL,
CONSTRAINT PK_Company PRIMARY KEY CLUSTERED (companyid),
CONSTRAINT FK_Company_Users FOREIGN KEY (userid) REFERENCES Users (userid)
);
CREATE TABLE CompanyType
(
companytypeid INT NOT NULL,
companyid INT NOT NULL,
CONSTRAINT PK_CompanyType PRIMARY KEY CLUSTERED (companytypeid),
CONSTRAINT FK_CompanyType_Company FOREIGN KEY (companyid) REFERENCES Company (companyid)
);
CREATE TABLE CompanyTypeCategory
(
companytypecategoryid INT NOT NULL,
companytypeid INT NOT NULL,
CONSTRAINT PK_CompanyTypeCategory PRIMARY KEY CLUSTERED (companytypecategoryid),
CONSTRAINT FK_CompanyTypeCategory_CompanyType FOREIGN KEY (companytypeid) REFERENCES CompanyType (companytypeid),
);
INSERT INTO Users (userid)
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
INSERT INTO Client (clientid,userid)
VALUES (6,10),(7,3),(8,8),(9,5),(10,6);
INSERT INTO Budget (budgetid)
VALUES (15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25);
INSERT INTO BudgetClient (budgetid,clientid)
VALUES (15,6),(16,7),(17,8),(19,9),(18,10),(21,6),(20,7),(25,8),(23,9),(24,10);
INSERT INTO Company (companyid,userid)
VALUES (1,7),(5,1),(8,9),(12,2),(15,4);
INSERT INTO CompanyType (companytypeid, companyid)
VALUES (1,1),(2,5),(3,8),(4,12),(5,15),(6,5),(7,8);
INSERT INTO CompanyTypeCategory (companytypecategoryid,companytypeid)
VALUES (1,7),(2,1),(3,3),(4,2),(5,1),(6,1),(7,5),(8,4),(9,6),(10,7);
The white/green lines, with the exception of the 'Type' column, are currently returned. The green line is the many-to-many table, and the red line is what I want returned as well (the other 'one to many' on related to the green 'BudgetClient' table). Essentially the recursion is using foreign keys to trace the table relationships. So here, the top/anchor table is Users, which has two child tables - Client and Company, both one to many relationships. In turn, Client has one related table, BudgetClient, in a many to many relationship with Budget - BudgetClient ties Client & Budget together.
To be clear, my desired result is that ALL of the lines in the image below would be returned.
