Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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
0
andieje
Asked:
andieje
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now