CODEWITHSUNDEEP
oracle-databaseplsql
MagnusH
MagnusH

Reputation: 13

Is there a way to make a PLSQL script that lists all columns that IS NULL for every record in a table?

I am working with a huge database with several columns and records. I want to browse a specific table and make a list of the columns that are empty for every record. Is this possible without refering to all the specific column names?

Thanks for help!

Upvotes: 1

Views: 88

Answers (3)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

It's possible but if you have a lot data it will last a long time.

create table xxx as select * from dba_objects where rownum < 10000;

prepare test table get table stats. It can be long lasting process.

begin
 dbms_stats.gather_table_stats(user,'XXX',estimate_percent =>100);
 -- ..
 -- others tables to analizye
end;

Generate reports. 

select table_name,column_name from user_tab_cols where coalesce(low_value,high_value) is null and table_name in('XXX');

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13527

You can use the below script to find out the null columns in your database - 

DECLARE
    COUNT_COL INT;
    SQL_STR VARCHAR2(100); 
BEGIN
    FOR I IN (SELECT OBJECT_NAME, COLUMN_NAME
              FROM USER_OBJECTS UO
              JOIN USER_TAB_COLS UTC ON UO.OBJECT_NAME = UTC.TABLE_NAME) LOOP
          SQL_STR := 'SELECT COUNT(1) FROM ' || I.OBJECT_NAME || ' WHERE ' || i.COLUMN_NAME || ' IS NOT NULL';
          EXECUTE IMMEDIATE SQL_STR INTO COUNT_COL;
          IF COUNT_COL = 0 THEN
              DBMS_OUTPUT.PUT_LINE(I.COLUMN_NAME);
          END IF;
    END LOOP;
END;

Here is the fiddle.

Upvotes: 0

Rustam Pulatov
Rustam Pulatov

Reputation: 665

Try for all record in table:

SELECT a.owner, a.table_name, b.column_name
 FROM all_tables a, all_tab_columns b
WHERE a.table_name = '<TABLE_NAME>'
AND a.table_name = b.table_name
AND a.num_rows = b.num_nulls

For all table

SELECT a.owner, a.table_name, b.column_name
  FROM all_tables a, all_tab_columns b
 WHERE a.table_name = b.table_name
 AND a.num_rows = b.num_nulls

Upvotes: 0

Related Questions