SQL Statement For All Columns Not Null

Question

I'm working on a query and my table is very large, many columns. I do not want to type out each individual column in the where clause- is there a way where I can select all without writing them, something like this (below does not work):

select *
from table t
where t.* is not NULL

Just type it out. If that's a problem, then the likelihood is that your table is poorly designed — Strawberry, Jun 01 '18 at 21:44
You can use `information_schema.columns` to write the expressions you want. — Gordon Linoff, Jun 01 '18 at 21:45
Looks like this is not possible in one line. Check this https://stackoverflow.com/questions/639531/search-in-all-fields-from-every-table-of-a-mysql-database — Ashik Basheer, Jun 01 '18 at 21:46
You can easily use other tools (such as excel) to build the entire statement without any trouble — Gustavo Topete, Jun 01 '18 at 21:48
Table is very long, typing is not an option (design is old and poor I agree) thanks for information_schema.columns — lucyb, Jun 01 '18 at 21:51

score 3 · Accepted Answer · answered Jun 01 '18 at 22:24

You can try to find fields from information_schema.columns table then execute dynamic sql.

like this.

SET @Efields := '';
SET @Esql:='';
SET @Table_Name = 'table1';

select @Efields := GROUP_CONCAT(COLUMN_NAME SEPARATOR ' IS NOT NULL AND ') 
from information_schema.columns
where TABLE_NAME = @Table_Name;
SELECT @Esql:=CONCAT('select * from ',@Table_Name ,'  WHERE ',@Efields,' IS NOT NULL ');

PREPARE stmt1 FROM  @Esql; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1;

sqlfiddle:https://www.db-fiddle.com/f/32PisE5bChKSVPzk6LjEP3/0

score 1 · Answer 2 · answered Jun 01 '18 at 22:16

1

An alternative, if you wanted, in your where clause do something like (assuming your number of columns in the table is 10):

Select T.*
from [TABLE] T
where 1|2|3|4|5|6|7|8|9|10 is not null

answered Jun 01 '18 at 22:16

DeFlanko

66
8

SQL Statement For All Columns Not Null

2 Answers2