Search a string in entire schema

[anu13]

Hi,

I need to search a string(which might be a keyword containing a wild card character) in all tables in an Oracle schema.
The result that I need is a table contaning the tablename, columnname and the exact sting in which the string was found.

Could someone help me with a suitable code?

Thanks in advance.

[Anas Mosaad]

You may create a stored procedure that take the keyword and schema name as input parameters and returns a table of all table names, columns and exact string. This procedure will query DBA_OBJECTS view to get all objects of type table in the target schema. Loop on all objects and get all columns names -not sure exactly which view that contains the column names- that are of string type and construct a query using the table name and columns name tp query that specific table. Execute the query using execute immediate. Finally, if a result was returned, add it to the table.

I'm not sure how efficient will be that procedure, but seems to me that it will take quite some time. Perhaps you will need to make that procedure pipelined.

Unfortunately, I'm not having an Oracle system currently to make a sample code for you. However, I hope the above description can help you.

[zmbd]

Respectfully, your thread is subject to being removed because asking for someone to provide completed code or a completed project is against site policy. Please consult the FAQ and specific forum sticky threads for further information.

If however, you'd like to share what work you've already attempted, perhaps someone here would be willing to help.

In the meantime some heavy reading for you:
Displaying Information About Schema Objects
Syntax for Schema Objects and Parts in SQL Statements

[JayaramN]

try the below query

select * from user_source where text like ('%<your keyword>%')

But it will look only on PROCEDURE, FUNCTION, TRIGGER
or
try to write a ananomouys SQQ block to check the keyword which you are searching is there in the table script sql.
Like:

declare
-- get List of all tables in your databsae 
-- select object_name from user_objects where object_type='TABLE'
begin
    for C1 in (cursor WHICH HAS all YOUR table ) loop
        --iterate each table
            select * from ( select DBMS_METADATA.GET_DDL('TABLE', C1.<table name>)  from DUAL) where TT like ('%<YOUR KEYWORD>%')
             if RETURNS RESULTS
                 YOU GOT the table
             else
                 YOUR KEYWORD is not in the table SCRIPT
             end;
    end loop;
end;

Please let me know if face any issue.