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