Solved

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

Posted on 2014-01-10
3
275 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
  • 2
3 Comments
 
LVL 142

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 142

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Creating and Managing Databases with phpMyAdmin in cPanel.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

778 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