0

I have a table with lots of columns, and I want to quickly figure out which columns are empty (full of NULL). I'm not sure how to loop over the columns of the table, so I first put the column names into a temporary table like so: 

CREATE TEMPORARY TABLE columnnames

SELECT column_name FROM information_schema.columns WHERE 
table_name='table';

Then I tried to do a SELECT to get only the columns that had COUNT=0:

SELECT * FROM columnnames WHERE ( SELECT COUNT(column_name) FROM 'table' ) > 0

The syntax doesn't check out though and I'm not sure how to get it working. Is there a better way to loop over the columns to find which ones are empty?

Martin
  • 22,212
  • 11
  • 70
  • 132
yunyun333
  • 131
  • 1
  • 7
  • Possible duplicate of [How do I check if a column is empty or null in mysql](https://stackoverflow.com/questions/8470813/how-do-i-check-if-a-column-is-empty-or-null-in-mysql) – Martin Jul 23 '19 at 22:15
  • what does empty columns mean to you? – pegasuspect Jul 23 '19 at 22:16
  • Might find a solution here - [Find all those columns which have only null values, in a MySQL table](https://stackoverflow.com/questions/12091272/find-all-those-columns-which-have-only-null-values-in-a-mysql-table) – Piyush Saxena Jul 23 '19 at 22:39
  • Any time I see the word 'loop' in a MySQL question, I think that this might be a problem better solved in a programming language — but you don't mention which (if any) language you are using. If you REALLY MUST do this in a MySQL-only way, then the above link from Piyush Saxena is your best bet. – Jerry Jul 23 '19 at 23:41

0 Answers0