Find all matching tables?

[RatiKanta pal]

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?

Database:oracle 10g.

Thank you.

[Jeanne Boyarsky]

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é]

Check the content of Jeanne's ALL_TAB_COLUMNS.

[RatiKanta pal]

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é]

You can query ALL_TAB_COLUMNS like any other table (WHERE COLUMN_NAME = 'IND')

[RatiKanta pal]

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]

For that you have to select all columns of all tables checking for that string.
That is a horrible requirement to have.

[Christophe Verré]

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]

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]

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.