gpt-4-0314· openai
Suzhen/CodeChat
in sql server, is there a way to get the entire chain of foreign keyed tables starting…
- User
in sql server, is there a way to get the entire chain of foreign keyed tables starting from a specific table?
- Assistant
In SQL Server, there isn't a built-in query to get the entire chain of foreign keyed tables starting from a specific table. However, you can use Common Table Expressions (CTE) and system catalog views to achieve this.
Here's a script that does this:
-- Replace 'YourTableName' with the starting table name you want to find the chain for DECLARE @TableName NVARCHAR(128) = 'YourTableName'; WITH CTE_FK_Chain (ParentTable, ParentColumn, ReferencedTable, ReferencedColumn, Level) AS ( -- Base case: Get tables directly referenced by the starting table SELECT OBJECT_NAME(fk.parent_object_id) AS ParentTable, COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn, OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable, COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn, 1 AS Level FROM sys.foreign_keys fk INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id WHERE OBJECT_NAME(fk.parent_object_id) = @TableName UNION ALL -- Recursive case: Keep finding tables referenced by the current Table SELECT OBJECT_NAME(fk.parent_object_id), COL_NAME(fkc.parent_object_id, fkc.parent_column_id), OBJECT_NAME(fk.referenced_object_id), COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id), c.Level + 1 FROM sys.foreign_keys fk INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id INNER JOIN CTE_FK_Chain c ON c.ReferencedTable = OBJECT_NAME(fk.parent_object_id) ) SELECT * FROM CTE_FK_Chain ORDER BY Level;This will give you the chain of foreign keyed tables in a hierarchical format, starting from the specified table. The
Levelcolumn indicates the depth of the foreign key relationship.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.