Skip to Main Content
  • Questions

  • Query dictionary for existing columns

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Pankaj.

Asked: April 23, 2020 - 8:35 am UTC

Last updated: April 24, 2020 - 3:12 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi
I have to add the 3 columns(like-: updated_time, inserted_time, updated_by) in all the tables of my schema, but some table have only 1 column from all 3, some have 2 columns from all 3 and some have all 3 columns, and some tables don't have any columns from all 3.

Now the task is how to get the tables name which have all 3 columns(all 3 columns should be present in tables)

2) how can get the tables which don't have all 3 columns or having 1 or 2 out of the given 3 columns.

Thanks in advance.

and Connor said...

Something like this perhaps ?

SQL> create table t1 ( x int );

Table created.

SQL> create table t2 ( x int );

Table created.

SQL> create table t3 ( x int );

Table created.

SQL> create table t4 ( x int );

Table created.

SQL>
SQL> alter table t2 add created date;

Table altered.

SQL>
SQL> alter table t3 add created date;

Table altered.

SQL> alter table t3 add modified date;

Table altered.

SQL>
SQL> alter table t4 add created date;

Table altered.

SQL> alter table t4 add modified date;

Table altered.

SQL> alter table t4 add whom varchar2(10);

Table altered.

SQL>
SQL>
SQL> select t.table_name, tc.cnt
  2  from   user_tables t,
  3         ( select table_name, count(*) cnt
  4           from   user_tab_columns
  5           where  column_name in ('CREATED','MODIFIED','WHOM')
  6           group by table_name
  7         ) tc
  8  where t.table_name = tc.table_name(+)
  9   /

TABLE_NAME                            CNT
------------------------------ ----------
T1
T2                                      1
T3                                      2
T4                                      3

4 rows selected.

SQL>
SQL>
SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

A reader, April 24, 2020 - 6:32 am UTC

Thanks Connor McDonald.

My task has been done with your help thanks a lot.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.