blob: 1ac50f46454e4b34c51d834d32e0232bf04fb491 (
plain)
1 MySql:Find all Required Columns
2 ===============================
3 :author: Aaron Ball
4 :email: nullspoon@iohq.net
5
6
7
8 I am currently working on a functionality for an app that automatically
9 enforces database null constraints in the client side code and in the server
10 side code (if null='no' then print * next to title and require the value be set
11 in the form postback). Basically, what I need to do is to query the database
12 for all columns that are Null="NO". Initially, I looked into the show
13 command...
14
15 ----
16 'SHOW Columns FROM dbname.tablename where `Null`='NO';
17 ----
18
19 That does almost what I want. However, this unfortunately returns more data
20 than I need, and I'd like to avoid parsing the data if I can get MySql to give
21 me only the data I want. After searching around a bit more, I discovered that
22 one of the default databases in MySql contains exactly what I needed:
23 **information_schema**.
24
25 The query to grab all fields that cannot be null is not actually too
26 complicated thankfully.
27
28 ----
29 SELECT column_name FROM information_schema.columns WHERE is_nullable='NO' && table_name='mytable' && table_schema='mydatabase';
30 ----
31
32 So here, we're grabbing the column_name field from the columns table within the
33 information_schema database provided the is_nullable field is equal to 'no';
34 The rest is simply filtering it all down so it only returns the column names
35 for our particular table (the table_name field) inside of our particular
36 database (the table_schema field).
37
38
39 Category:MySQL
40
41
42 // vim: set syntax=asciidoc:
|
