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!

how can i get list of columns used in all tables ?

Discussion in 'SQL PL/SQL' started by sasanka, Nov 7, 2014.

  1. sasanka

    sasanka Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    Is there a solution on how can i get list of columns used in all tables where in the column names are to be specified dynamically for all tables under a owner.

    For example :-

    SELECT TABLE_NAME , COLUMN_NAME
    FROM ALL_TAB_COLUMNS
    WHERE COLUMN_NAME LIKE '%EMP_NUM%' ;


    This will give me the list of tables having the columns EMP_NUM which i'm explicitly mentioning.

    How can i make the column names dynamic , i.e for all columns in all tables
    for a schema.
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    What do you mean by dynamic ? Column name as a parameter or for all column names.

    Here under 2 examples:

    1) ALL_TAB_COLUMNS gives table_name/column_name for ALL owners; you can either use USER_TAB_COLUMNS or add a condition on the OWNER: in this example, you input Owner and Column_name
    2) This will give you all the columns and in which tables they are found: enter Owner as parameter

     
  3. sasanka

    sasanka Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    i wanted to get for all the column names from all the tables under a schema.
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Then I suppose Option 2 gives the solution.
     
  5. sasanka

    sasanka Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi ,

    Thank you. This will give me the results only if the two tables have an exact column name.
    how can i get the results for the below scenario

    Table1 Column_name Table2 Column_name
    Employee Emp_num Compensation Emp_number

    how can i get the results even if the column names are partially matched .

    Thanks.
     
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    You can always use the LIKE operator like you did in your initial post:

    However, it may be misleading; for example, if a column name = "ID", using LIKE on this column will lead to column names like USER_ID, EMP_ID, IDENT, SLIDE, etc... and you can imagine all the possible combinations.
     
    sasanka likes this.