user3875610 user3875610 - 5 months ago 11
SQL Question

Get all column from a database where column is not Null

I have a mysql database, and I wanted to pull all the tables and their corresponding column names. I managed to do this using the following:

SELECT `TABLE_NAME`, `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='Database';


But I wanted only column which are not Null for all rows, I tired the following but this did not work:

SELECT `TABLE_NAME`, `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='Database' AND `COLUMN_NAME` IS NOT NULL;


But the result was identical to my previous query

Structure of the sample table from the database:

ID Entity_ID Balance Security Purpose
A1 ADF1 32131 412 NULL
A2 JFJ4 51551 NULL NULL

Answer

try this:

SELECT `TABLE_NAME`,  `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='Database' AND `IS_NULLABLE` = 'NO';
Comments