Tuesday, April 17, 2012

Script to find column(s) with all 'Null' values in large table


If a table has million of rows and several columns then below script can be use to find out how many columns in a table have no data/ null value

set serveroutput on

DECLARE

v_count number;
v_count_null number;

BEGIN

execute immediate 'select count(*) from PROPERTY_QUOTE' into v_count;

FOR  i in (select column_name from user_tab_cols where table_name = 'table_name')

LOOP

select count(*) into count_null from table_name  where i.column_name IS NULL;
IF
v_count_null = v_count then

dbms_output.put_line(i.column_name);

END IF;

END LOOP;

END;

* add your table name in red text

1 comment:

  1. Modified a bit...


    DECLARE

    v_count number;
    v_count_null number;

    BEGIN

    select count(*) into v_count from HM101 ;

    FOR i in (select column_name from user_tab_cols where table_name = 'HM101')
    LOOP

    execute immediate 'select count(*) from HM101 where '||i.column_name||' IS NULL' into v_count_null;

    IF v_count_null = v_count then

    dbms_output.put_line(i.column_name);

    END IF;

    END LOOP;

    END;
    /

    ReplyDelete