Oracle: Finding Columns with only null values

SELECT  t.column_name
FROM    user_tab_columns t
WHERE   t.nullable = 'Y'
        AND t.table_name = 'YOUR_TABLE_NAME'
        AND t.num_distinct = 0

select
  count(col_1),
  count(col_2),
  count(col_3)
from
  <table>

returns how many records per column have a non-null value (at least in Oracle, that is.)

For example

drop table tq84_count_nulls;

create table tq84_count_nulls (
  col_1  varchar(50),
  col_2  number,
  col_3  date
);

insert into tq84_count_nulls values (null, null, null);
insert into tq84_count_nulls values ('xx', null, null);
insert into tq84_count_nulls values (null,   42, null);
insert into tq84_count_nulls values ('yy',   12, null);

select
  count(col_1),
  count(col_2),
  count(col_3)
from
  tq84_count_nulls;

returns

COUNT(COL_1) COUNT(COL_2) COUNT(COL_3)
------------ ------------ ------------
           2            2            0

indicating that col_3 consists of nulls only.

This idea can then be used to create the desired view.

The table now needs also the *group_id*:

drop table tq84_count_nulls;
create table tq84_count_nulls (
  col_1    varchar(50),
  col_2    number,
  col_3    date,
  group_id varchar(2)
);

insert into tq84_count_nulls values (null, null, null, 'a');
insert into tq84_count_nulls values ('xx', null, null, 'a');
insert into tq84_count_nulls values (null,   42, null, 'a');
insert into tq84_count_nulls values ('yy',   12, null, 'a');

insert into tq84_count_nulls values (null, null, null, 'b');
insert into tq84_count_nulls values (null, null, null, 'b');
insert into tq84_count_nulls values (null,   42, null, 'b');
insert into tq84_count_nulls values (null,   12, null, 'b');




create or replace view nulls_per_type as 
with n as (
  select
    count(col_1) col_1_count,
    count(col_2) col_2_count,
    count(col_3) col_3_count,
    group_id
  from
    tq84_count_nulls
  group by 
    group_id
),
o as (
select case col_1_count when 0 then 'COL_1 is always 0 for ' || group_id else null end u from n union all
select case col_2_count when 0 then 'COL_2 is always 0 for ' || group_id else null end u from n union all
select case col_3_count when 0 then 'COL_3 is always 0 for ' || group_id else null end u from n
)
select * from o where u is not null;

Which, when selected returns:

select * from nulls_per_type;

COL_1 is always 0 for b
COL_3 is always 0 for a
COL_3 is always 0 for b

After looking at @Gerrat and @BQ's comments, I thouht I could get the details I need in the following way: I have a legacy table that has N different types. All types share columns, and have exclusive columns.

I can create a view for each type with all columns, then use all_tab_columns to get all column names where the "num_nulls" is less than the total number of rows for that specific type.

From there it should be easy to gather columns that are used for each type and create the views.

Thoughts?

I think you can solve this using metaprogramming. Use a cursor to loop through each type and column, and use 'not exists' to check if the column is empty. For example:

CREATE TABLE result_table (type VARCHAR(50), column VARCHAR(50))

CURSOR c IS
    SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = &table_name;

CURSOR ct IS
    SELECT DISTINCT type_name FROM &table_name;

BEGIN

FOR t in ct
LOOP
    FOR r in c
    LOOP
        --If you're confused about how this works, replace 'EXECUTE IMMEDIATE'
        --with print or something and look at the output
        EXECUTE IMMEDIATE
            'INSERT INTO result_table SELECT ''' ||
                t.type_name || ''', ''' || r.COLUMN_NAME ||
                ''' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ' || 
                &table_name || ' WHERE t.type_name = ''' || t.type_name ||
                ''' AND ' || r.COLUMN_NAME || ' IS NOT NULL);';
    END LOOP
END LOOP

SELECT * FROM result_table

Apologies if there's a mistake in the syntax somewhere, I have nothing to check this on.

You can identify by using the below query :

select * from (select ascii(t.col2)+ascii(t.col4)+ascii(t.col1)+ascii(t.col3) col from test_null_col t)
where col is null;

And you want to delete null column rows here is query :

delete from
(select ascii(t.col2)+ascii(t.col4)+ascii(t.col1)+ascii(t.col3) col from test_null_col t)
where col is null;

To find rows that have a null-value, use the "is null" condition.

select * from table_A where table_col1 is null;

To do the reverse, and find all the rows with a non-null value, use the "is not null" condition:

select * from table_A where table_col1 is not null;

Nulls in range comparisons:

select * from table_A where table_col1 < 15 or table_col1 is null;

Something like this?

SELECT column1, column2, column3 -- and so on
FROM tableA
WHERE columnX IS NULL
AND columnY IS NULL
AND columnZ IS NULL;

Obviously, you can use that in a CREATE VIEW... statement if you like as well.

SELECT tablecolumn, tablecolumn2, ... FROM TABLENAME
WHERE column IS NOT NULL