Need a Query to search a value("Number") from all tables/views

Question

I use Oracle 11G database and work in Toad. I need a query to search a number(9901) in all tables and views. I found some PL/SQL codes but they are working for strings. I have knowledge about SQL but I do not know PL/SQL. My user is SYS, I think I have privilege to make such a query. Can you help for this case?

Answer 1

I did not gather if you wanted to search the names of the tables/columns or the actual values, contained in the tables.

In the former case your goal can be accomplished by a simple query (replace the values in the filter with the proper ones (but leave the % symbols)):

select 
    owner, 
    table_name, 
    column_name
from 
    dba_tab_columns
where
    owner = '<TABLE_OWNER>'
    and ( table_name like '%<SEARCH_VALUE>%'
        or column_name like '%<SEARCH_VALUE>%' );

---

Searching the actual table data is a bit more complicated... Here is a sample plan for executing that kind of search:

1. Narrow the search scope - filter by table owner ( you really do not want to search in the SYS or SYSTEM tables... )
2. Identify potential match candidates - in our case tables which have numeric columns, since we are searching for a number
3. Check if each table has the desired value anywhere in it
4. Find out the exact columns, which contain the desired value

Here is a sample PL/SQL script, which performs the search and prints the table and column names ( change _SCHEMA_NAME_ to the proper value ):

declare
    v_owner varchar2( 30 ) := '_SCHEMA_NAME_';
    v_search_value number := 9901;

    v_query_template varchar2( 500 ) := 'select ''x'' from dual where exists ( select 1 from <OWNER>.<TABLE> where <FILTER> )';
    v_filter clob;
    v_query clob;
    v_match char(1);
begin
    -- loop through all tables which are potential candidates - have numeric columns
    for v_tab_row in
    (
        select distinct owner, table_name
        from dba_tab_columns
        where 
            owner = v_owner
            and data_type in ( 'NUMBER', 'FLOAT' )
    ) loop
        v_filter := '';

        -- loop through each table column in order to build general match query
        for v_col_row in
        (
            select owner, table_name, column_name
            from dba_tab_columns
            where 
                owner = v_tab_row.owner
                and table_name = v_tab_row.table_name
                and data_type in ( 'NUMBER', 'FLOAT' )
        ) loop
            v_filter := v_filter || v_col_row.column_name || ' = ' || v_search_value || ' or ';
        end loop;
            v_filter := rtrim( v_filter, ' or ' );


        v_query := replace( v_query_template, '<OWNER>', v_owner );
        v_query := replace( v_query, '<TABLE>', v_tab_row.table_name );
        v_query := replace( v_query, '<FILTER>', v_filter );

        -- debug output:
        --dbms_output.put_line( v_query );

        begin

            -- check if the table contains the search value anywhere in it
            -- if not foung throws NO_DATA_FOUND exception and the loop continues
            execute immediate v_query into v_match;

            -- print the table name
            dbms_output.put_line( v_tab_row.owner || '.' || v_tab_row.table_name );

            -- loop through each table column to check which one of them contains the desired value
            for v_col_row in
            (
                select owner, table_name, column_name
                from dba_tab_columns
                where 
                    owner = v_tab_row.owner
                    and table_name = v_tab_row.table_name
                    and data_type in ( 'NUMBER', 'FLOAT' )
            ) loop
                begin
                    v_query := replace( v_query_template, '<OWNER>', v_owner );
                    v_query := replace( v_query, '<TABLE>', v_tab_row.table_name );
                    v_query := replace( v_query, '<FILTER>', v_col_row.column_name || ' = ' || v_search_value );

                    -- debug output:
                    --dbms_output.put_line( v_query );

                    -- check if the specific column contains the desired value
                    -- if not foung throws NO_DATA_FOUND exception and the loop continues
                    execute immediate v_query into v_match;

                    -- print the column name
                    dbms_output.put_line( v_col_row.owner || '.' || v_col_row.table_name || '.' || v_col_row.column_name );
                exception
                when NO_DATA_FOUND 
                    then 
                        -- do nothing
                        null;
                end;
            end loop;
        exception
            when NO_DATA_FOUND 
                then 
                    -- do nothing
                    null;
        end;
    end loop;
end;

The code can be easily modified to perform text searches as well - just include varchar2, nvarchar2, char and nchar data types in the loop queries.

Important note! - If you have many/large tables in your database this will run very slowly and might put high load on the system. After all, multiple full table scans will be involved.

---

For the views, you will need to check the code for each view on the reported tables and check if it contains any of the reported columns.