Trying to join columns to key_column_usage

Question

I'm trying to pull in following information but am having an issue with my join syntax:

I need the following:

• table_schema
• table_name
• table_column
• column_type for table referenced
• table_schema
• referenced_table_name
• referenced_table_column
• column_type for referenced_table

I have this but it's not working (I know the syntax isn't correct -- but it's my starting point)

select kcu.table_schema, kcu.table_name, kcu.column_name, c.column_type, kcu.referenced_table_schema, kcu.referenced_table_name, kcu.referenced_column_name, c.column_type
from key_column_usage kcu
inner join columns c on kcu.table_schema=c.table_schema and kcu.table_name=c.table_name and kcu.column_name=c.column_name
where kcu.referenced_table_name='table_to_check' and kcu.referenced_column_name='column_to_check';

Answer 1

Your query was syntactically correct but you were missing a additional JOIN to the columns table to get the column_type for kcu.referenced_column_name - your version joins once and displays the referencing column_type twice.

This second JOIN should be on the referenced_table_schema, referenced_table_name and referenced_column_name columns.

e.g.

SELECT kcu.table_schema, kcu.table_name, kcu.column_name, c1.column_type, 
       kcu.referenced_table_schema, kcu.referenced_table_name, 
       kcu.referenced_column_name, c2.column_type
  FROM information_schema.key_column_usage kcu 
  JOIN information_schema.columns c1 
    ON c1.table_schema = kcu.table_schema AND 
       c1.table_name   = kcu.table_name AND 
       c1.column_name  = kcu.column_name
  JOIN information_schema.columns c2 
    ON c2.table_schema = kcu.referenced_table_schema AND 
       c2.table_name   = kcu.referenced_table_name AND 
       c2.column_name  = kcu.referenced_column_name
 WHERE kcu.referenced_table_schema = 'schema_to_check' 
   AND kcu.referenced_table_name   = 'table_to_check' 
   AND kcu.referenced_column_name  = 'column_to_check';