Find required (non nullable) columns in MySQL database

The query below lists all non-nullable columns in a database (schema).

Query

select tab.table_schema as database_name,
    tab.table_name,
    col.ordinal_position as column_id,
    col.column_name,
    col.data_type,
    case when col.numeric_precision is not null
            then col.numeric_precision
        else col.character_maximum_length end as max_length,
    case when col.datetime_precision is not null
            then col.datetime_precision
        when col.numeric_scale is not null
            then col.numeric_scale
        else 0 end as 'precision'
from information_schema.tables as tab
join information_schema.columns as col
        on col.table_schema = tab.table_schema
        and col.table_name = tab.table_name
        and col.is_nullable = 'no'
where tab.table_schema not in ('information_schema', 'sys', 
                               'mysql','performance_schema')
      and tab.table_type = 'BASE TABLE'
      -- and tab.table_schema = 'database name'
order by tab.table_schema,
         tab.table_name,
         col.ordinal_position;

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

Columns

  • database_name - name of the database (schema)
  • table_name - name of the table
  • column_id - position of the column in a table
  • column_name - name of the column
  • data_type - column data type
  • max_length - maximum length of the data type
  • precision - precision of the data type

Rows

  • One row: represents one non-nullable column in a table
  • Scope of rows: all non-nullable columns in all tables in a specified database (schema)
  • Ordered by: table database name, table name, column position in table

Sample results

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