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

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.
ndoeterniettoeAsked:
Who is Participating?
 
paquicubaConnect With a Mentor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
paquicubaCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sujith80Connect With a Mentor Commented:
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
 
JGCoughlinConnect With a Mentor Commented:
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
 
awking00Connect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.