List table columns with their foreign keys in MariaDB database

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


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,

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


  • table - table name with database (schema) name
  • col_id - column number in a table
  • column_name - name of the column
  • 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


  • 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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.