Is there a way to test for null on all columns in a wildcard select?

Question

PSEUDO:

select * from foo where [every column is not null]

Is there a way to do this without specifying the actual column names?

Answer 1

A possible solution that I can think of involves using dynamic-SQL

SELECT GROUP_CONCAT(column_name SEPARATOR ' IS NOT NULL AND ')
   INTO @sql
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_schema = SCHEMA()
   AND table_name = 'foo'
 GROUP BY table_name;

SET @sql = CONCAT('SELECT * FROM foo WHERE ', @sql, ' IS NOT NULL');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is SQLFiddle demo

---

Obviously it can be wrapped into a stored procedure with a parameter for a table name

DELIMITER $$
CREATE PROCEDURE sp_select_all_not_null(IN tbl_name VARCHAR(64))
BEGIN
  SELECT GROUP_CONCAT(column_name SEPARATOR ' IS NOT NULL AND ')
     INTO @sql
    FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_schema = SCHEMA()
     AND table_name = tbl_name
   GROUP BY table_name;

  SET @sql = CONCAT('SELECT * FROM ', tbl_name, ' WHERE ', @sql, ' IS NOT NULL');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END
DELIMITER ;

And then use it

CALL sp_select_all_not_null('foo');

Here is SQLFiddle demo for that