Find required (non nullable) columns in MariaDB 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

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