aspose file tools*
The moose likes Oracle/OAS and the fly likes Find all matching tables? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Find all matching tables?" Watch "Find all matching tables?" New topic
Author

Find all matching tables?

RatiKanta pal
Ranch Hand

Joined: Nov 13, 2011
Posts: 76

Hi all,

I have one specific requirment.I have on data field name 'IND'.I want to find all the matching tables having 'IND' as a data members.

How to write query for that?

Databaseracle 10g.

Thank you.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31057
    
232

There is a system table ALL_TAB_COLUMNS which has the table names and column names. You can query it just like any other table - except in this case by column name.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

Check the content of Jeanne's ALL_TAB_COLUMNS.


[My Blog]
All roads lead to JavaRanch
RatiKanta pal
Ranch Hand

Joined: Nov 13, 2011
Posts: 76

Thank you for the response.But it seems like that store the column information of all tables.But I want to search all the matching table name containing 'IND' data.
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

You can query ALL_TAB_COLUMNS like any other table (WHERE COLUMN_NAME = 'IND')
RatiKanta pal
Ranch Hand

Joined: Nov 13, 2011
Posts: 76

Hi Christophe Verré,

My tables are like this.

Table1

col1 col2 col3
---- ---- ----
abc IND PAK
xyz IND PAK
pqr IND PAK




Table2

col1 col2 col3
---- ---- ----
abc us PAK
xyz us PAK
pqr us PAK



Table3

col1 col2 col3
---- ---- ----
abc IND a
xyz IND b
pqr IND c

Now i want to display the table name which have 'IND' as a data.(TABLE1,TABLE3)

How to write query for that??
E Armitage
Rancher

Joined: Mar 17, 2012
Posts: 892
    
    9
For that you have to select all columns of all tables checking for that string.
That is a horrible requirement to have.
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

As E Armitage said in his post, what kind of requirement is that ?
You could still use ALL_TAB_COLUMNS to query all columns from all tables, but really, do you have to do that ?
RatiKanta pal
Ranch Hand

Joined: Nov 13, 2011
Posts: 76

Yes,
I have a huge database system have large number of records.And we have to do some changes to that perticular data,In which ever table that data is used.


Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31057
    
232

StackOverflow has a post where someone wrote pl/sql to search the entire database. It's going to be slow as this is not a good requirement to have. I hope you don't need to run it often.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Find all matching tables?