Reputation: 13
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
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
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
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