Searching for table names and columns names in the database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Searching for table names and columns names in the database

  1. #1
    Join Date
    Sep 2012
    Posts
    4

    Searching for table names and columns names in the database

    Hi all.. i am new to oracle database and try to retrieve all the column names and tables names for a particular number (column value from one table). I am using following two different queries but getting no results and no errors. any help??

    query1 :
    select table_name, column_name from
    (select rownum,table_name, column_name, dbms_xmlgen.getxml
    ('select 1 from "'||table_name||'" where "'||column_name||'"=&number') x
    from user_tab_columns where data_type='NUMBER') where length(x)!=0;

    query2:

    select table_name,
    column_name
    from( select table_name,
    column_name,
    to_number(
    extractvalue(
    xmltype(
    dbms_xmlgen.getxml(
    'select count(*) c from ' || table_name ||
    ' where NUMBER(' || column_name || ') = ''589773'''
    )
    ),
    'ROWSET/ROW/C'
    )
    ) cnt
    from (select utc.*, rownum
    from user_tab_columns utc
    where data_type in ('NUMBER') ) )
    where cnt >= 0

    please let me know if u have any questions

    Thanks!

  2. #2
    Join Date
    Sep 2012
    Posts
    4
    In addition to this, i am using 11g client for 10g oracle database.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Let me check my understanding of your business requirements, is the idea to get a list of the tables that include a particular column name?

    Like... select table_name, column_name from user_tab_columns where column_name = 'whatever-column-name';
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Sep 2012
    Posts
    4
    Thanks PAVB for replying back .

    No i dont know column names ..I am looking for tables names and column names that has a particular value in it. for example the reuslt shld look like this

    Table column value
    table1 column1 abc
    table 4 column10 abc
    table6 column3 abc

    does it make sense?

    thanks!

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by CornerQuery View Post
    Thanks PAVB for replying back .

    No i dont know column names ..I am looking for tables names and column names that has a particular value in it. for example the reuslt shld look like this

    Table column value
    table1 column1 abc
    table 4 column10 abc
    table6 column3 abc

    does it make sense?

    thanks!
    do you mean... a particular value as part of the table_name and/or column_name OR a particular value stored in a particular column?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    think he means search every column in every table

    http://asktom.oracle.com/pls/asktom/...42426083757635 has a function to do that

  7. #7
    Join Date
    Sep 2012
    Posts
    4
    yeah ..search for column names and table names in the database for a same value stored in that column.

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by CornerQuery View Post
    yeah ..search for column names and table names in the database for a same value stored in that column.
    That's an unusual requirement, please follow Davey's lead.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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



Click Here to Expand Forum to Full Width