File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Find all matching tables?

 
RatiKanta pal
Ranch Hand
Posts: 84
Eclipse IDE Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 33723
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Christophe Verré
Sheriff
Pie
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Check the content of Jeanne's ALL_TAB_COLUMNS.
 
RatiKanta pal
Ranch Hand
Posts: 84
Eclipse IDE Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can query ALL_TAB_COLUMNS like any other table (WHERE COLUMN_NAME = 'IND')
 
RatiKanta pal
Ranch Hand
Posts: 84
Eclipse IDE Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 989
9
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 84
Eclipse IDE Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 33723
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic