Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    64

    Unanswered: Tracking occurance of columns in DB Tables

    Hi folks,

    I have column name 'XYZ' which is appearing in multiple tables in Database. There are some 500 tables in the DB.

    is there way i can find in how many tables this column 'XYZ' is appearing i.e list of tables whic is having this column 'XYZ'.

    Please advice

    Cheers!
    Subho

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    SELECT DISTNCT table_name 
    FROM all_tab_columns
    WHERE column_name = 'XYZ';
    More details in the manual:
    http://download.oracle.com/docs/cd/B...views_part.htm

  3. #3
    Join Date
    Jan 2010
    Posts
    64
    Many Thanks!!! Its working fine .....

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by shammat View Post
    Code:
    SELECT DISTNCT table_name 
    FROM all_tab_columns
    WHERE column_name = 'XYZ';
    More details in the manual:
    Part II
    You don't need distinct. A table can't have the same column name twice. I would do

    Code:
    SELECT table_name,owner
    FROM all_tab_columns
    WHERE column_name = 'XYZ';
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by beilstwh View Post
    You don't need distinct. A table can't have the same column name twice.
    That's right. If you include the owner, then you don't need it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •