Select Tables that do not contain a certain column in Oracle 12c

user_11516402 0
Viewed 99

Most of the tables in the DB have a certain column 'col_abc'. How can I find the tables that do not contain this column 'col_abc'.

The query

select  table_name
from  user_tab_columns
where  column_name not in ('COL_ABC')

does not return expected result.

Thanks, Amit

oracle12c oracle sql
4 Answers

Here is one way:

select table_name from user_tables
minus
select table_name from user_tab_columns
where column_name = 'ABC';
user_187470

The Below Query will give the expected results. Please modify <ur_Schema_name> as the required schema name

SELECT * FROM ALL_TABLES at
WHERE at.OWNER = <ur_Schema_name>
AND NOT EXISTS (SELECT 1 FROM ALL_TAB_COLUMNS atc
                   WHERE atc.TABLE_NAME = at.TABLE_NAME
                    AND atc.OWNER =at.OWNER
                    AND atc.COLUMN_NAME='COL_ABC'
                    AND atc.OWNER=<ur_Schema_name>);
user_153657180

You could also use this solution using LEFT JOIN. It is a kind of NOT EXISTS clause.

select t.* 
from user_tables t
left join (
  select table_name
  from user_tab_columns
  where column_name = 'COL_ABC'
) tc
on (t.table_name = tc.table_name)
where tc.table_name is null
;
user_136191160

If you only need to look in the current user's schema, this is probably the simplest way to write the query:

select table_name
from   user_tables
where  table_name not in (
                           select table_name
                           from   user_tab_columns
                           where  column_name = 'COL_ABC'
                         )
;

This uses in a crucial way the fact that the column table_name in user_tab_columns is constrained not null.

If you need to look across all schemas, you need to use the dba_ or all_ dictionary views, and look for (owner, table_name) pairs instead of just table_name. Left as further exercise for you.

Admin1
Related