MySql:Find all Required Columns

I am currently working on a functionality for an app that automatically enforces database null constraints in the client side code and in the server side code (if null=no then print * next to title and require the value be set in the form postback). Basically, what I need to do is to query the database for all columns that are Null="NO". Initially, I looked into the show command…​

'SHOW Columns FROM dbname.tablename where `Null`='NO';

That does almost what I want. However, this unfortunately returns more data than I need, and I’d like to avoid parsing the data if I can get MySql to give me only the data I want. After searching around a bit more, I discovered that one of the default databases in MySql contains exactly what I needed: information_schema.

The query to grab all fields that cannot be null is not actually too complicated thankfully.

SELECT column_name FROM information_schema.columns WHERE is_nullable='NO' && table_name='mytable' && table_schema='mydatabase';

So here, we’re grabbing the column_name field from the columns table within the information_schema database provided the is_nullable field is equal to no; The rest is simply filtering it all down so it only returns the column names for our particular table (the table_name field) inside of our particular database (the table_schema field).

Category:MySQL