diff options
Diffstat (limited to 'src/MySql:Find_all_Required_Columns.adoc')
| -rw-r--r-- | src/MySql:Find_all_Required_Columns.adoc | 43 |
1 files changed, 43 insertions, 0 deletions
diff --git a/src/MySql:Find_all_Required_Columns.adoc b/src/MySql:Find_all_Required_Columns.adoc new file mode 100644 index 0000000..62ba323 --- /dev/null +++ b/src/MySql:Find_all_Required_Columns.adoc @@ -0,0 +1,43 @@ +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: |