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
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: 62

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30293
    
150

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: 14687
    
  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: 62

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: 14687
    
  16

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

Joined: Nov 13, 2011
Posts: 62

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: 14687
    
  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: 62

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30293
    
150

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?