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,293 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
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!

 
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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

756 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