MYSQL only Show columns which allow null

Question

I need to be able to pull the column names which allow a NULL value, I know that

show columns from TABLE

Will give show me the table properties and whether or not the column allows null values, but is there a way to just return ONLY the columnnames which allow null.

show columns from TABLE where Null = 'YES' doesn't work, but it explains what I need to accomplish.

And of course it's easy to just pull everything and sort it out later on, but if there IS a way to do what I'm asking, I'd like to learn it.

Answer 1

Yes you can do it by using the information_schema database..

use the Query

SELECT COLUMN_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA='db name' 
  AND TABLE_NAME='table Name' 
  AND IS_NULLABLE='YES';

Answer 2

SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE IS_NULLABLE = 'Yes'

Run describe on information_schema.columns to refine the results to your liking

Answer 3

SHOW COLUMNS FROM `table_name` WHERE `Null` = 'YES'

Above query works. Try to avoid querying to information_schema directly