The following script tells you which table is a special value in the database. I want to find the columns of the tables found

Question

The following script allows you to find a custom value in the database.

I can find out which table this special value is. But I can't figure out which column the table is in. how do I find the column name of the table finded.

declare
v_match_count integer;
v_counter integer;
v_owner varchar2(255) := 'HASTANE';

v_data_type varchar2(255) := 'NUMBER';

v_search_string varchar2(4000) := 1455671;


v_sql clob := '';
begin
for cur_tables in (select owner, table_name from all_tables where owner = v_owner and table_name in 
(select table_name from all_tab_columns where owner = all_tables.owner and data_type like '%' || upper(v_data_type) || '%')
order by table_name) loop
v_counter := 0;
v_sql := '';


for cur_columns in (select column_name from all_tab_columns where 
owner = v_owner and table_name = cur_tables.table_name and data_type like '%' || upper(v_data_type) || '%') loop
if v_counter > 0 then
v_sql := v_sql || ' or ';
end if;
v_sql := v_sql || 'upper(' || cur_columns.column_name || ') like ''%' || upper(v_search_string) || '%''';
v_counter := v_counter + 1;
end loop;

v_sql := 'select count(*) from ' || cur_tables.table_name || ' where ' || v_sql;

execute immediate v_sql
into v_match_count;



if v_match_count > 0 then
dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records');
end if;
end loop;



exception
when others then
dbms_output.put_line('Error when executing the following: ' || dbms_lob.substr(v_sql, 32600));
end;

/

why not to use one Loop and just join `all_tab_columns` to `all_tables`? — hotfix, May 13 '19 at 08:39
thanks.you're right, it could make sense to join the two of them. — sadabat, May 14 '19 at 06:47

score 0 · Accepted Answer · answered May 13 '19 at 11:43

This doesn't seem like the kind of code you'd run in production, so I figure performance is not your primary concern. Given that, why not execute a separate query for each column name, and not load them all into a single query with lots of ORs? Perhaps something like what you see below. I moved your anonymous block into a stored procedure to make it more easily reusable.

CREATE OR REPLACE PROCEDURE find_cols_with_values (owner_in      IN VARCHAR2,
                                                   datatype_in   IN VARCHAR2,
                                                   value_in      IN VARCHAR2)
IS
   v_match_count   INTEGER;
   v_sql           CLOB := '';
BEGIN
   FOR cur_tables
      IN (  SELECT owner, table_name
              FROM all_tables
             WHERE     owner = owner_in
                   AND table_name IN (SELECT table_name
                                        FROM all_tab_columns
                                       WHERE     owner = all_tables.owner
                                             AND data_type LIKE
                                                       '%'
                                                    || UPPER (datatype_in)
                                                    || '%')
          ORDER BY table_name)
   LOOP
      FOR cur_columns
         IN (SELECT column_name
               FROM all_tab_columns
              WHERE     owner = owner_in
                    AND table_name = cur_tables.table_name
                    AND data_type LIKE '%' || UPPER (datatype_in) || '%')
      LOOP
         v_sql :=
               'select count(*) from '
            || cur_tables.table_name
            || ' where upper('
            || cur_columns.column_name
            || ') like ''%'
            || UPPER (value_in)
            || '%''';

         EXECUTE IMMEDIATE v_sql INTO v_match_count;

         IF v_match_count > 0
         THEN
            DBMS_OUTPUT.put_line (
                  'Match in '
               || cur_tables.owner
               || ': '
               || cur_tables.table_name
               || '.'
               || cur_columns.column_name
               || ' - '
               || v_match_count
               || ' records');
         END IF;
      END LOOP;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
            'Error when executing the following: '
         || DBMS_LOB.SUBSTR (v_sql, 32600));
END;
/

CREATE TABLE t1 (mynum NUMBER)
/

CREATE TABLE t2 (yournum VARCHAR2 (100))
/

BEGIN
   INSERT INTO t1
        VALUES (123);

   INSERT INTO t1
        VALUES (456);

   INSERT INTO t2
        VALUES ('123');

   COMMIT;
END;
/

BEGIN
   find_cols_with_values (owner_in      => USER,
                          datatype_in   => 'NUM',
                          value_in      => '123');
END;
/

Thanks, it works. How can I improve performance? because it took about 7 minutes. this method can take a long time in large databases. — sadabat, May 14 '19 at 06:57
Check out this Q&A - it might help you. https://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle — Steven Feuerstein, Jun 14 '19 at 13:26

The following script tells you which table is a special value in the database. I want to find the columns of the tables found

1 Answers1