Re: Finding Columns with All Null Values

Halbert09 wrote:
>
> What kind of script can I use to display all columns names in a table
> or tables where the column has nothing but NULL values in all the rows
> of the table?

This is just an untested quick hack, but I'm sure you get the picture.

declare
  i number;
  cursor l_tabcur is
    select TABLE_NAME, COLUMN_NAME
    from user_tab_columns
    where table_name not like '%$%'
      and data_type NOT IN ('LONG','LONG RAW','BLOB');   sqlStmnt varchar2(2000);
begin
  dbms_output.enable(1000000);
  for r_tabcur in l_tabcur loop
    sqlStmnt := 'select count(distinct(' || r_tabcur.COLUMN_NAME || ')) from ' || r_tabcur.TABLE_NAME || '';

    dbms_output.put_line(sqlStmnt);
    execute immediate sqlStmnt INTO i;
    if i=0 then
      dbms_output.put_line(r_tabcur.COLUMN_NAME||' in '||r_tabcur.TABLE_NAME||' contains nothing but NULL');

    else
      dbms_output.put_line(r_tabcur.COLUMN_NAME||' in '||r_tabcur.TABLE_NAME||' contains something');

    end if;
  end loop;
end;
/

Take care that l_tabcur must not select any columns from data types which does not work with "select distinct(count...)..." like LONG etc.

Regards,

Knut Received on Thu Jun 20 2002 - 10:55:30 CDT