See which columns of the table contain the specified string (such as which columns of the table the ' Andy ' exists in)

--View the columns in the table that contain the specified characters.
Sql> select * from Demo1;


NAME ID
------------------------------ ----------
???? 4
Andy 1
Andy1 2
Andy2 3
Liudehua 3
???? 5


6 rows selected.


Sql> select * from Demo2;


NAME ID
------------------------------ ----------
Andy 1
Andy2 3
Andy2 3
Liudehua 3


Sql> select * from Demo3;


NAME ID
------------------------------ ----------
Andy 1
Andy2 3
Andy2 3
Liudehua 3


Sql> set Serveroutput on






Sql>declare
V_sql VARCHAR2 (4000);
V_tb_column VARCHAR2 (4000);
V_CNT number (18,0);
Cursor cur is select ' Select ' | | ' "' | | t1.table_name| | ' "." ' | | T1. column_name| | ' ' | | ', ' | | ' as COL_NAME, NVL (COUNT (t. "' | | T1. column_name| | ' "), 0) as cnt from" ' | |
t1.table_name| | ' t WHERE t. ' | | t1.column_name| | ' like ' '%andy% ' as Str
From cols T1 left join User_col_comments T2
on T1. Table_name=t2. TABLE_NAME and T1. Column_name=t2. column_name
Left JOIN User_tab_comments T3
on T1. Table_name=t3. table_name
WHERE not EXISTS (SELECT T4. object_name from user_objects T4
WHERE T4. Object_type= ' TABLE '
and T4. temporary= ' Y '
and T4. Object_name=t1. TABLE_NAME)
and (t1. Data_type= ' CHAR ' or T1. Data_type= ' VARCHAR2 ' or T1. Data_type= ' VARCHAR ')
--and T1.table_name= ' Run_stats '
ORDER by T1. TABLE_NAME, T1. column_id;

BEGIN
For i in Cur LOOP
V_sql: = I.str; --Get the SQL statement that will be executed;
EXECUTE IMMEDIATE v_sql into V_tb_column, v_cnt;
IF v_cnt > 0 Then
Dbms_output.put_line (' Table ' | | substr (V_tb_column,1,instr (V_tb_column, '. ',)-1) | | ' col ' | | substr (V_tb_column, '. ', v_tb_column,instr) +1) | | ' has string ' Andy ';
END IF;
END LOOP;
EXCEPTION when OTHERS Then
BEGIN
Dbms_output.put_line (V_sql);
Dbms_output.put_line (V_tb_column);
END;
END;
/






Result output:
Table "DEMO1" col "NAME" has string "Andy"
Table "DEMO2" col "NAME" has string "Andy"
Table "DEMO3" col "NAME" has string "Andy"
Table "TEAM" col "MENTOR" has string "Andy"

PL/SQL procedure successfully completed.

Reference: http://bbs.csdn.net/topics/350154546

See which columns of the table contain the specified string (such as which columns of the table the ' Andy ' exists in)