Lists column names that do not have any values.

hi Experts,

I would like to get a SQL to SQL query which lists column names and datatypes that do not have any value in  all columns of all tables  in a schema.

In other words suppose a table has 12 columns and 500 rows. I want to know, if there is a column in this table, where are all 500 rows DO Not have any value. Which could be called an Un-USED Column.

Thanks
Kamal AgnihotriAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
just change the IN clause to be whatever tables you're interested in.
This will look up the columns and check which have nulls or don't


SELECT owner, table_name, column_name
  FROM (SELECT t.owner,
               t.table_name,
               c.column_name,
               DBMS_XMLGEN.getxmltype(
                      'select count('
                   || c.column_name
                   || ') X from '
                   || t.owner
                   || '.'
                   || t.table_name
               ).EXTRACT('/ROWSET/ROW/X/text()').getnumberval()
                   notnullcount
          FROM dba_tables t, dba_tab_cols c
         WHERE t.owner = c.owner
           AND t.table_name = c.table_name
           AND t.table_name IN ('TABLE1','TABLE2'))
 WHERE notnullcount = 0
0
sdstuberCommented:
same idea, but more efficient because it will stop searching as soon as it finds a value rather rather than wasting time counting them all.

SELECT owner, table_name, column_name
  FROM (SELECT t.owner,
               t.table_name,
               c.column_name,
               DBMS_XMLGEN.getxmltype(
                      'select 1 X from '
                   || t.owner
                   || '.'
                   || t.table_name
                   || ' where '
                   || c.column_name
                   || ' is not null and rownum = 1'
               ).EXTRACT('/ROWSET/ROW/X/text()').getnumberval()
                   nullcheck
          FROM dba_tables t, dba_tab_cols c
         WHERE t.owner = c.owner
           AND t.table_name = c.table_name
           AND t.table_name IN ('TABLE1','TABLE2'))
 WHERE nullcheck IS NULL
0
awking00Commented:
declare
v_sql varchar2(255);
v_cnt pls_integer;
begin
for t in
(select table_name, column_name
 from user_tab_columns
 where table_name = 'YOURTABLE'
) loop
v_sql := 'select count('||t.column_name||') from '||t.table_name;
--dbms_output.put_line(v_sql);
execute immediate v_sql into v_cnt;
if v_cnt = 0 then
 dbms_output.put_line(t.table_name||' has all null values in column '||t.column_name);
end if;
end loop;
end;
/
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
awking00 - that's a similar approach to my first post,

if you want to go with pl/sql block I suggest following something like my second post so you don't expend resources reading extra rows unnecessarily.  

that might look something like this..

DECLARE
    v_sql VARCHAR2(255);
    v_cnt PLS_INTEGER;
BEGIN
    FOR t IN (SELECT owner, table_name, column_name
                FROM dba_tab_columns
               WHERE table_name = 'YOUR_TABLE')
    LOOP
        v_sql :=
               'select 1 from '
            || t.owner
            || '.'
            || t.table_name
            || ' where '
            || t.column_name
            || ' is not null and rownum = 1';

        BEGIN
            --dbms_output.put_line(v_sql);
            EXECUTE IMMEDIATE v_sql INTO v_cnt;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                v_cnt := 0;
        END;

        IF v_cnt = 0
        THEN
            DBMS_OUTPUT.put_line(
                   t.owner
                || '.'
                || t.table_name
                || ' has all null values in column '
                || t.column_name
            );
        END IF;
    END LOOP;
END;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
Sorry, Sean, I submitted my response before I was your post :-)
0
awking00Commented:
I was ? How about I saw? Another example of the oft futility of spellcheck.
0
Kamal AgnihotriAuthor Commented:
Excellent Job. Thanks a lot.
0
Mark GeerlingsDatabase AdministratorCommented:
If your tables are analyzed, here is a *MUCH* more efficient approach:

select tc.owner, tc.table_name, tc.column_name
from dba_tables t, dba_tab_columns tc
where t.temporary = 'N'               -- ignore temporaryv tables
  and t.tablespace_name is not null   -- ignore external tables
  and t.owner not in ('SYS','SYSTEM') -- ignore "system" tables
  and tc.owner = t.owner
  and tc.table_name = t.table_name
  and tc.nullable = 'Y'
  and tc.num_nulls = t.num_rows
order by tc.owner, tc.table_name, tc.column_id;

Note: This line will also exclude partitioned tables:
  and t.tablespace_name is not null

If you have partitioned tables and you want them to be included, you will need a different way to include them but still exclude external tables.  (Maybe: "and t.last_analyzed is not null" would do that.)
But you still may need to handle partitioned tables in a separate query where you compare the num_rows of individual partitions rather than of the whole table.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.