Solved

Oracle query/procedure to bring back all table.columns in schema where all rows for a given column are null

Posted on 2007-03-27
9
3,294 Views
Last Modified: 2013-12-19
Hi,

I'm looking to write an Oracle SQL query or procedure to bring back all table.columns in the schema where all rows for a given column are null. For example:

TABLE:
ID     X     Y     Z
1              A     E
2              B     F
3              C     G
4              D

The query should bring back:
TABLE.X
as this is the only column where all rows are null.

Does anyone have an idea how to do this?

Thanks in advance.
0
Comment
Question by:ndoeterniettoe
[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
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18799547
this will be a very intensive research, as there is no way to query that directly.
you will have to loop on all tables / all columns, and count the null values...
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 18799660
Execute the following procedure in SQL*Plus:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER);


You can then check the column's density. Empty columns should have zero density:

SELECT TABLE_NAME||'.'||COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE DENSITY = 0
/


You can also check the number of rows against the num of null values:

SELECT X.TABLE_NAME||'.'||X.COLUMN_NAME
FROM ALL_TAB_COLUMNS X, ALL_TABLES Y
WHERE X.TABLE_NAME = Y.TABLE_NAME
AND Y.NUM_ROWS = X.NUM_NULLS
/


These two views can also provide you with useful info:
USER_TAB_COL_STATISTICS
ALL_TAB_COL_STATISTICS


0
 
LVL 23

Accepted Solution

by:
paquicuba earned 64 total points
ID: 18799722
The second query sould give you more accurate results, since density is not really for checking null columns, and empty columns may have zero density in certain cases...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 62 total points
ID: 18807026
Procedurally it can be done as shown below.(Take care, this code will hit every table in your schema!!)

declare
 l_num number := 0;
begin
 for rec in (select table_name from user_tables ) loop
  for rec2 in (select column_name from user_tab_columns where table_name = rec.table_name) loop
   execute immediate 'select count(*) from '||rec.table_name||
                     ' where '||rec2.column_name||' is not null' into l_num;
   if l_num = 0 then
    dbms_output.put_line(rec.table_name||'.'||rec2.column_name);
   end if ;
  end loop;
 end loop;
end;
/
0
 
LVL 1

Assisted Solution

by:JGCoughlin
JGCoughlin earned 62 total points
ID: 18847114
The code above is a great start.  Unfortunately, performance may need to be improved, and there is a possibilty that you will be looking at items other than tables
this is not completely tested, but I think with some tinkering you can get it.  I always throw some small rownums in to test these types of scripts, and increase them until I feel I have tested enough tables and columns to run my script without restriction.  You could materialize the all_views result set and index the view_name column for performance if you needed in this case.  The reason the "inner" code will run a lot faster is that you only need to look for "1" non-null row, instead of counting them all.  I tried to run this and ran into some strange problems that are due to user_tab_columns having column names with spaces in them, thus the extra look for items that are not views.

declare
l_num number := 0;
begin
  for rec in (select table_name, column_name from user_tab_columns atc where not exists (select null from all_views where view_name = atc.table_name) and rownum < 330) loop
--     dbms_output.put_line(rec.table_name||'.'||rec.column_name);
   begin
   execute immediate 'select 1 from dual'||
                     ' where EXISTS (select null from '||rec.table_name||' where '||rec.column_name||' is not null)' into l_num;
   
   exception
   when no_data_found then
     dbms_output.put_line('ISNULL');
   end;
  end loop;
end;
/
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 62 total points
ID: 18934809
declare
v_count      number;
v_sql varchar2(255);

begin
for rec in
(select table_name, column_name
 from dba_tab_columns
 where owner = 'YOUR_SCHEMA_NAME')
loop
v_sql := 'select count(*) from '||rec.table_name||
         ' where '||rec.column_name||' is not null';
execute immediate v_sql into v_count;
if v_count = 0 then
dbms_output.put_line(rec.table_name||'.'||rec.column_name);
end if;
end loop;
end;
/
Note - if you run this from the intended schema, you can substitute user_tab_columns for dba_tab_columns and remove the "where owner = ..." statement.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

710 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