The query below returns all columns from all tables in the user databases (schemas) with a foreign key reference if the column has one.

Query

select concat(col.table_schema, '.', col.table_name) as 'table',
       col.ordinal_position as col_id,
       col.column_name as column_name,
       case when kcu.referenced_table_schema is null
            then null
            else '>-' end as rel,
       concat(kcu.referenced_table_schema, '.', kcu.referenced_table_name)
              as primary_table,
       kcu.referenced_column_name as pk_column_name,
       kcu.constraint_name as fk_constraint_name
from information_schema.columns col
join information_schema.tables tab
     on col.table_schema = tab.table_schema
     and col.table_name = tab.table_name
left join information_schema.key_column_usage kcu
     on col.table_schema = kcu.table_schema
     and col.table_name = kcu.table_name
     and col.column_name = kcu.column_name
     and kcu.referenced_table_schema is not null
where col.table_schema not in('information_schema','sys',
                              'mysql', 'performance_schema')
      and tab.table_type = 'BASE TABLE'
--    and fks.constraint_schema = 'database name'
order by col.table_schema,
         col.table_name,
         col.ordinal_position;

Note: if you need the information for a specific database (schema), then uncomment the table_schema line and provide your database name.

Columns

• table - table with its database (schema) name
• col_id - column number in a table
• column_name - column name
• rel - relationship symbol ('>-') indicating the foreign key and its direction
• primary_table - referenced table
• pk_column_name - referenced column
• fk_constraint_name - foreign key constraint name

Rows

• One row: represents one column of every table in a database (schema)
• Scope of rows: all columns from all tables in a database/schema
• Ordered by: table database (schema) and name, column id in a table

Sample results