List all table columns with details (PKs, UKs, FKs, Default, Computed, etc.) in MySQL database

Article for: Teradata

Query below lists all columns in database with their details like uniqueness, is foreign key etc.


select col.table_schema as database_name,
       case when col.data_type in ('datetime', 'timestamp', 'time')
                 then col.datetime_precision
            else col.numeric_precision end as 'precision',
       col.character_maximum_length as char_length,
       case when (group_concat(constraint_type separator ', '))
                  like '%PRIMARY KEY%'
            then 'YES' else 'NO' end as PK,
       case when (group_concat(constraint_type separator ', '))
                  like '%UNIQUE%'
            then 'YES' else 'NO' end as UQ,
       case when (group_concat(constraint_type separator ', '))
                  like '%FOREIGN KEY%'
            then 'YES' else 'NO' end as FK,
from information_schema.columns col
join information_schema.tables tab
     on col.table_schema = tab.table_schema
     and col.table_name = tab.table_name
     and tab.table_type = 'BASE TABLE'
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
left join information_schema.table_constraints tco
     on kcu.constraint_schema = tco.constraint_schema
     and kcu.constraint_name = tco.constraint_name
     and kcu.table_name = tco.table_name
where col.table_schema not in('information_schema', 'sys',
                              'performance_schema', 'mysql')
group by 1,2,3,4,5,6,7,8,9,13
order by col.table_schema,


  • database_name - database (schema) name
  • table_name - table name
  • column_name - column name
  • data_type - type of data column contains
  • precision - precision of numeric types or number of fractional digits for datetime types
  • numeric_scale - scale for numeric data types
  • char_length - maximum character length
  • column_default - column's default value
  • PK - indicate if column is primary key
  • FK - indicate if column is foreign key
  • UQ - indicate if column must have unique value in table
  • is_nullable - indicate if column is nullable


  • One row represents one column
  • Scope of rows: represent all columns in database
  • Ordered by table schema, table name, columnname

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.