Oracle: Searching a record within schema

Searching a record in a schema tables.

This support wildcard and character. One can change the datatype. The search will be excruciating long time for bigger database.

undef searchme

SET SERVEROUTPUT ON SIZE 100000

    DECLARE

      match_count INTEGER;

    BEGIN

      FOR table_rec IN (select table_name, column_name  from user_tab_columns  where data_type like '%CHAR%') LOOP

        EXECUTE IMMEDIATE

          'SELECT COUNT(*) FROM ' ||  table_rec.table_name || ' WHERE '||table_rec.column_name||' like  :1'

          INTO match_count

          USING '&&searchme%';

                    IF match_count > 0 THEN

            dbms_output.put_line('&searchme found in the following tables and columns');     

            dbms_output.put_line('');

            dbms_output.put_line('Table Name      Column Name     Counts');

            dbms_output.put_line('________________________________________________');

          dbms_output.put_line( table_rec.table_name ||'    '||table_rec.column_name||'      '||match_count );

          END IF;

      END LOOP;

    END;

         /

VPXADMIN> @searchme

Enter value for searchme: Fabric.5

old   9:           USING '&&searchme';

new   9:           USING 'Fabric.5';

old  11:                  dbms_output.put_line('&searchme found in the following tables and columns');

new  11:                  dbms_output.put_line('Fabric.5 found in the following tables and columns');

Fabric.5 found in the following tables and columns

Table Name      Column Name     Counts

________________________________________________

TEST321    NETWORK      1

VPXADMIN>

Found a record in table name TEST321 and the column name is NETWORK.