CODEWITHSUNDEEP
mysql
Anubhav Dikshit
Anubhav Dikshit

Reputation: 1829

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

Upvotes: 1

Views: 2403

Answers (2)

praveen
praveen

Reputation: 1375

I hope it will help.

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

Upvotes: 1

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

try this:

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

Upvotes: 4

Related Questions