Solved

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

Posted on 2014-01-10
3
280 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

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…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 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