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,289 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 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
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 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

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.

Question has a verified solution.

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

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

809 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