asked on

Oracle query/procedure to bring back all table.columns in schema where all rows for a given column are null

Hi,

I'm looking to write an Oracle SQL query or procedure to bring back all table.columns in the schema where all rows for a given column are null. For example:

TABLE:
ID X Y Z
1 A E
2 B F
3 C G
4 D

The query should bring back:
TABLE.X
as this is the only column where all rows are null.

Does anyone have an idea how to do this?

Thanks in advance.

Guy Hengel [angelIII / a3]

this will be a very intensive research, as there is no way to query that directly.
you will have to loop on all tables / all columns, and count the null values...

paquicuba

Execute the following procedure in SQL*Plus:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER);

You can then check the column's density. Empty columns should have zero density:

SELECT TABLE_NAME||'.'||COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE DENSITY = 0
/

You can also check the number of rows against the num of null values:

SELECT X.TABLE_NAME||'.'||X.COLUMN_NAME
FROM ALL_TAB_COLUMNS X, ALL_TABLES Y
WHERE X.TABLE_NAME = Y.TABLE_NAME
AND Y.NUM_ROWS = X.NUM_NULLS
/

These two views can also provide you with useful info:
USER_TAB_COL_STATISTICS
ALL_TAB_COL_STATISTICS

ASKER CERTIFIED SOLUTION

paquicuba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION