CODEWITHSUNDEEP
sqldatabaseoracle10g
user1706047
user1706047

Reputation: 359

Finding list of all the tables those use a common column

I have a requirement where i need to find out all the tables those are having a common column.


My requirement is kind of simillar to below Example:
A school Teacher is assigned to many tasks for many divisions. Each of the division is maintained as a table.So all the division tables should have a column that can point to Teacher Id.But the name of the column may be different. For Ex: DivA-TeacherId, DivB-TeacherId .... , DivN-TeacherId


Also there is a table called Teacher which contains the details of teachers.

Table Teacher - Column TeacherId (Primary Key)

The teacher moves out of the school and a new teacher replaces him and takes the same roles and responsibilities of the old teacher. So here we need to update all the dependancy tables with new teacher.

We may have 100 number of tables. So its difficult to findout all those tables manually .

Is there a query, we can find all those tables which have dependancy upon this column, so that those can be updated with new TeacherId.(Please note that, the naming of this column may not be same in all the dependancy tables.But all those column names may have a common substring "TeacherId")

Please suggest if any query can be used to findout the solution for above one.

Upvotes: 0

Views: 78

Answers (3)

Multisync
Multisync

Reputation: 8787

select     uc.constraint_name fk_ref_to_source,
           ucc1.table_name table_ref_to_source,
           ucc1.column_name column_ref_to_source,
           ucc2.table_name source_table,
           ucc2.column_name source_column
    FROM   user_constraints uc, user_cons_columns ucc1, user_cons_columns ucc2
   WHERE       uc.constraint_name = ucc1.constraint_name
           AND uc.r_constraint_name = ucc2.constraint_name
           AND ucc1.position = ucc2.position -- Correction for multiple column primary keys.
           AND uc.constraint_type = 'R'
           AND ucc2.table_name = 'TEACHER'
ORDER BY   ucc1.table_name, uc.constraint_name;

user_constraints contains information about user constraints
user_cons_columns contains information about columns which form the constraint

This query will generate updates for your case:

SELECT     'update ' || ucc1.table_name || ' set ' || ucc1.column_name || ' = newTeacherId ' || ' where ' || ucc1.column_name || ' oldTeacherId;' sql
    FROM   user_constraints uc, user_cons_columns ucc1, user_cons_columns ucc2
   WHERE       uc.constraint_name = ucc1.constraint_name
           AND uc.r_constraint_name = ucc2.constraint_name
           AND ucc1.position = ucc2.position -- Correction for multiple column primary keys.
           AND uc.constraint_type = 'R'
           AND ucc2.table_name = 'TEACHER'
           AND ucc2.column_name = 'TEACHERID';

Upvotes: 2

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

Let's understand it with an example.

SQL> SELECT a.owner,
  2    a.table_name,
  3    b.owner primary_owner,
  4    b.table_name primary_table
  5  FROM user_constraints a,
  6    user_constraints b
  7  WHERE a.table_name      = 'EMP'
  8  AND a.r_constraint_name = b.constraint_name
  9  /

OWNER      TABLE_NAME PRIMARY_OWNER   PRIMARY_TABLE
---------- ---------- --------------- ---------------
SCOTT      EMP        SCOTT           DEPT

SQL>

The above query makes use of the referential integrity. The table DEPT references table EMP. The column names has nothing to do with table reference.

In your case, replace EMP with your table name. And the query will list all the tables referencing your table.

You can read the documentation for *_CONSTRAINTS

Upvotes: 2

SGeis
SGeis

Reputation: 266

You cann use the system table ALL_TAB_COLUMNS

select * from all_tab_columns
    where column_name LIKE '%Teacher%'

Upvotes: 1

Related Questions