1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

To find the number of occurrence of a column in all the tables

Discussion in 'SQL PL/SQL' started by sharo, Jul 3, 2011.

  1. sharo

    sharo Active Member

    Messages:
    6
    Likes Received:
    1
    Trophy Points:
    65
    Hello,

    If there is a column, say "Salary" and there are a large number of tables in the database.
    1)I need to find in which tables or the count of the number of occurrences of that column in all the tables.
    2) Moreover, I also need to know, how many columns have the value of Salary as 10000.

    Is there any way by which this can be achieved with the help of SQL queries, then please let me know.


    Thanks,
    Sharo....
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    You need to check Oracle's system tables and you can find all table names with a particular column. If memory serves me right, there is a system table ALL_TAB_COLUMNS which contains columns of user's tables, views and clusters.
     
    sharo likes this.
  3. sharo

    sharo Active Member

    Messages:
    6
    Likes Received:
    1
    Trophy Points:
    65
    Thanks but is there any other way to find a particular value in that column (as explained in point 2 above)?
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    I am afraid not. You may be able to do some gymnastics by writing a procedure. Search for all tables with your column and then search for values in that column within your procedure.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This link may help you