How to find Number of null column in table using PL/SQL

A database has a lot of columns (more than 100). Some of these columns have null entries. How can I find out how many columns have null entries in开发者_JAVA百科 at least one row, without manually testing each and every column?

---

Try:

declare
  l_count integer;
begin
  for col in (select table_name, column_name 
              from user_tab_columns where table_name='EMP')
  loop
    execute immediate 'select count(*) from '||col.table_name
                      ||' where '||col.column_name
                      ||' is not null and rownum=1'
      into l_count;
    if l_count = 0 then
      dbms_output.put_line ('Column '||col.column_name||' contains only nulls');
    end if;
  end loop;
end;

---

Try analyzing your table (compute statistics, don't estimate) and then (immediately) do:

select column_name, num_nulls
from all_tab_columns
where table_name = 'SOME_TABLENAME'
and owner = 'SOME_OWNER';

Of course as data later changes, this will become slightly more incorrect. If you need to get more fancy and do a field population count (fieldpop), then you'll need to loop through all rows and check for nulls explicitly (and exclude any other values you deem "not populated", perhaps a default of 0 for a number field for example).

---

I can give you the direction in which to research:

Check "user_tab_columns" through which you can get information related to columns in a table. E.g.

select count(*) from user_tab_columns where table_name = 'YOURTABLENAME'

This gives you the number of columns in that table.

Together with this you would need to use a cursor, i think, to check each column for null values rather than adding a null check in WHERE clause for each column.

---

This will give you the number of NULL column values per row of data:

declare
  TYPE refc IS REF CURSOR; 
  col_cv refc; 
  l_query varchar(3999);
  v_rownum number;
  v_count number;
begin
  l_query := 'select rownum, ';
  for col in (select table_name, column_name 
              from user_tab_columns where table_name='EMP')
  loop
    l_query := l_query ||'DECODE('||col.column_name||',NULL,1,0)+';  
  end loop;
  l_query := l_query||'+0 as no_of_null_values from EMP';

DBMS_OUTPUT.PUT_LINE(l_query);

OPEN col_cv FOR l_query;
LOOP
FETCH col_cv into v_rownum, v_count;
EXIT WHEN col_cv%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_rownum || ' ' || v_count);

END LOOP;
CLOSE col_cv;

end;

I feel dirty even writing it! (It won't work when the number of columns in the table is very large and l_query overflows).

You just need to change the table name (EMP above).