finding the columns in a table where all of the rows are empty or null

Hello

I think the question title says it all. I have a massive table with loads of columns and loads of rows. I believe lots of these are unused. Please could you tell me how I would run a query to find all of the columns in a table where all of the rows in that table are empty  ('') or NULL.

Thanks a lot
andiejeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
> I believe lots of these are unused
to start with, please note that even if a column has only NULL values doesn't mean it isn't used ...

apart from that:
select count(*) cnt
, sum(case when field1 is null then 0 else 1 end) field1_filled
, sum(case when field2 is null then 0 else 1 end) field2_filled
 .... etc for all the fields of your table ...
 from yourtable 

Open in new window


run that query, and you will get a total count of the rows (cnt), and for each field the number of rows with non-nulls.
if that value is 0, all rows are null for that column
0
 
andiejeAuthor Commented:
Thanks - what about if the field is empty is that just (case when field = '' .....) etc
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for all fields that are text data types, you change to:

, sum(case when field1 is null then 0 when field1 = '' then 0 else 1 end) field1_filled
0
All Courses

From novice to tech pro — start learning today.