searching in all columns from all tables for specific data

[erixire]

Hi everybody,

I am totally new to database (and so Oracle) and I want to know how to do something. Suppose for example I want to know EVERY columns in ALL tables that contains the word "TOTO" (the word can be inside another word like TATOTOTA), is there a way to output something that will show me all the tables names (and the associate column names) that contains the word I am searching for?

Thanks a lot.

 

[lagay]

select *
from all_tab_columns
where column_name like '%TOTO%'

You may want to eliminate SYS and SYSTEM as owners in the query unless you are intersted in Oracle system tables.

 

[erixire]

thanks lagay, but this is not what I want to do. I want to search in every column (I don't know the name of the column and maybe there is more than one column in the same table that as the same data information) of every table for a specific data. So is there a table in Oracle that has all the information on every tables and columns? What I want to know is the name of every tables and the name of the columns that contains the information 'TOTO'.

Thanks again to everybody that can help me!

 

[lagay]

I'm not sure if you getting column and data confused here. Are you looking for column names that contain 'TOTO' or data within the table/column that contains 'TOTO'? If you are looking for the data, you can use the all_tab_columns Oracle table in a cursor to get all columns in all tables and then build a dynamic sql statement for each table and check if each column contains data 'TOTO'. If this is what you are looking for, hope your database is relatively small. Hope this helps.

 

[erixire]

Hi lagay,

Excuse me if it was a little bit confused: yes, this is what I want to do (looking for data within the table/column that contains 'TOTO'). And thanks for your answer, I wanted to know if there was an easier way to do that. But it seems not, so I will try to learn PL/SQL to do the kind of job I want to do. I don't know much about cursor and all this, but I understand that I have to do some programming to do what I want to do (we have a small database for now).

Thanks again for your great help.