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