Check if is column nullable in MySQL database

This query returns nullability information of the specified column.

Query

select c.table_schema as database_name,
       c.table_name,
       c.column_name,
       case c.is_nullable
            when 'NO' then 'not nullable'
            when 'YES' then 'is nullable'
       end as nullable
from information_schema.columns c
join information_schema.tables t
     on c.table_schema = t.table_schema 
     and c.table_name = t.table_name
where c.table_schema not in ('mysql', 'sys', 'information_schema',
                             'performance_schema')
      and t.table_type = 'BASE TABLE'
      -- and t.table_schema = 'database_name' -- put your database name here
order by t.table_schema,
         t.table_name,
         c.column_name;

Columns

  • database_name - name of database (schema)
  • table_name - name of table
  • column_name - name of column
  • nullable - nullability attribute for the column:
    • is nullable - is nullable
    • not nullable - is not nullable

Rows

  • One row nullability of the specified column
  • Scope of rows - all columns in the database
  • Order by - table database, table_name, column name

Sample results

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