write a sql query that lists the entire chain of foreign keyed tables starting from…
- User
write a sql query that lists the entire chain of foreign keyed tables starting from tblEXAMPLE till the end of the chain of foreign keyed relations. I also want to make sure the results don't give me back duplicate table names.
- Assistant
To write a SQL query that lists the entire chain of foreign keyed tables starting from tblEXAMPLE till the end of the chain of foreign keyed relations, we can use a recursive Common Table Expression (CTE). Here is the query:
WITH RECURSIVE chain (tname, related_table) AS ( SELECT table_name AS tname, referenced_table_name AS related_table FROM information_schema.key_column_usage WHERE table_name = 'tblEXAMPLE' AND referenced_table_name IS NOT NULL UNION ALL SELECT c.related_table AS tname, k.referenced_table_name AS related_table FROM chain c JOIN information_schema.key_column_usage k ON c.related_table = k.table_name WHERE k.referenced_table_name IS NOT NULL ) , all_tables AS ( SELECT tname FROM chain UNION SELECT related_table FROM chain ) SELECT DISTINCT tname FROM all_tables;This query uses a recursive CTE to find all related tables starting from the 'tblEXAMPLE' table. It then combines the table names and related table names into a single column, 'tname', in the 'all_tables' CTE. Finally, it selects the distinct table names from the 'all_tables' to ensure that there are no duplicate table names in the result.