Solved

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

Posted on 2014-01-10
3
281 Views
Last Modified: 2014-01-14
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
Comment
Question by:andieje
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39771234
> 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
 

Author Comment

by:andieje
ID: 39771569
Thanks - what about if the field is empty is that just (case when field = '' .....) etc
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39772184
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question