MySql:Find all Required Columns
===============================
:author: Aaron Ball
:email: nullspoon@iohq.net


== {doctitle}

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


// vim: set syntax=asciidoc: