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.

Query

select col.table_schema as database_name,
       col.table_name,
       col.column_name,
       col.data_type,
       case when col.data_type in ('datetime', 'timestamp', 'time')
                 then col.datetime_precision
            else col.numeric_precision end as 'precision',
       col.numeric_scale,
       col.character_maximum_length as char_length,
       col.column_default,
       col.generation_expression,
       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,
        col.is_nullable
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,
         col.table_name,
         col.column_name;

Columns

  • 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

Rows

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

Sample results

0
There are no comments. Click here to write the first comment.