CODEWITHSUNDEEP
sqloracle-database
user1753180
user1753180

Reputation: 87

plsql List all table.column containing null values

I'd like to find all column of a set of table with null values in them.

I can find the table and column names

SELECT TABLE_NAME, COLUMN_NAME 
FROM user_tab_cols 
where nullable='Y'
      and table_name in ('myTalbe', 'myTable2');

And also check if the are nulls

select count(*) from myTable where myColumn is null;

but how can I put this toghether to have as result

table_name     column_name
myTable        myColumn
myTable        myCol2
myTable2       col4

Upvotes: 0

Views: 858

Answers (2)

Connor McDonald
Connor McDonald

Reputation: 11581

Here's a routine I wrote a while back to do exactly that. It will output the required DDL to make those columns that are nullable (but do not contain any nulls) not nullable.

https://connormcdonald.wordpress.com/2016/03/11/tightening-up-your-data-model/

It can do the task for a schema or a single table.

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

An approach could be with some dynamic SQL, but this would require some PL/SQL code; the following only uses SQL to get the result you need:

select *
from (
        select table_name,
               column_name,
               to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name || ' where ' || column_name || ' is null')),'/ROWSET/ROW/C')) as rowcount
        from user_tab_columns
        where nullable='Y'
          and table_name in ('myTalbe', 'myTable2')
     )
where rowcount > 0

This could be an approach with dynamic SQL:

declare
    type tableOfNumber is table of number;
    type tableOfChar   is table of varchar2(30);
    --
    vSQl            varchar2(4000);
    vListNumbers    tableOfNumber;
    vListTables     tableOfChar;
    vListColumns    tableOfChar;
begin
    select listagg( 'select ''' ||
                       table_name || ''' as table_name, ''' || 
                       column_name || ''' as column_name, count(*) as rowCount from ' || 
                       table_name || 
                       ' where ' || 
                       column_name || 
                       ' is null having count(*) > 0' ,
                     ' UNION ALL '
                   ) within group ( order by table_name, column_name)
    into vSQL
    from user_tab_columns
    where nullable='Y'
          and table_name in ('myTalbe', 'myTable2');
    --
    dbms_output.put_line(vSQL);


    /* whatever you may want to do with the query */


    /* for example, fetch into some variables and print the result */
    execute immediate vSQL
    bulk collect into vListTables, vListColumns, vListNumbers;
    --
    if vListTables.count() > 0 then
        for i in vListTables.first .. vListTables.last loop
            dbms_output.put_line('Table ' || vListTables(i) ||
                                    ', column ' || vListColumns(i) ||
                                    ', number of nulls: ' || vListNumbers(i)
                                  );
        end loop;
    end if;
end;

Upvotes: 1

Related Questions