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