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,286 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
9 Comments
 
LVL 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 31

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
UNIX SCP 5 49
Oracle regular expression 6 30
null value 15 70
VB.Net - Oracle BulkCopy from CSV Date Format 7 26
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now