How to search all columns of all tables in a database for a keyword?

[abdulgani]

Dear Team,

i have an requirement that : i want to search all the columns of all the tables in the particular database based on the specific key word or an free text.

example :

table 1: columns data
empname sam
empid 01

table 2 columns data
deptname sam
departmentid 10

table 3 columns data
organization name sam
organization id 1

when i search for text " SAM"

it should search me from the entire database, all tables and columns of it and display the result

output : tablename cloumn value
table1 empname sam
table2 deptname sam
table3 organizationame sam

the example is just an sample not the real data .

please help me with sample code or any link related to it .


thanks in advance

[debasisdas]

you need to use cursor for that using a number of OR conditions.

[Pilgrim333]

Hi,

You can also try to write a procedure to do this for you. I think this would be easier. Make use of the all_tables view etc.

Pilgrim.

[abdulgani]

Can you please provide any link that will help me out or any code . thanx in advance

[amitpatel66]

The following code would help you to proceed further. It works fine for columns of data type number,varchar, char etc but not for LONG.

You will need to check the following:

1. If you want to check for all the tables of a database then you need to make sure that SELECT for all the tables is GRANTED to the schema from where you are running this code
2. Convert LONG TO VARCHAR explicitly and then do the comparison.

declare
cursor c1 is select table_name from all_tables WHERE owner = (SELECT user FROM dual);
col_names SYS_REFCURSOR;
TYPE dat_res IS RECORD(tab_name VARCHAR2(100),col_name VARCHAR2(100));
TYPE dr is table of dat_res;
act_dat dr:= dr();
status NUMBER := 0;
cnt NUMBER := 1;
sql_stmt VARCHAR2(10000);
tab_name1 VARCHAR2(100);
col_name1 VARCHAR2(100);
BEGIN
FOR I IN C1 LOOP
sql_stmt:= 'SELECT table_name,column_name FROM all_Tab_cols WHERE table_name = '||CHR(39)||I.table_name||CHR(39);
OPEN col_names FOR sql_stmt;
LOOP
FETCH col_names INTO tab_name1,col_name1;
EXIT WHEN col_names%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'SELECT 1 FROM '||tab_name1|| ' WHERE '||'TO_CHAR('||col_name1||') LIKE '||CHR(39)||'BCD'||CHR(39) INTO status;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error In table: '||tab_name1||' Column:'||col_name1||' '||SQLERRM);
END;
IF (status = 1) THEN
act_dat.extend;
act_dat(cnt).tab_name:= tab_name1;
act_dat(cnt).col_name:= col_name1;
status := 0;
cnt:= cnt + 1;
END IF;
END LOOP;
CLOSE col_names;
END LOOP;
dbms_output.put_line('Table Name : Column Name');
FOR K IN act_dat.FIRST..act_dat.LAST LOOP
dbms_output.put_line(act_dat(K).tab_name||' : '||act_dat(K).col_name);
END LOOP;
END;
 
SQL> /
Error In table: PLAN_TABLE Column:OTHER ORA-00932: inconsistent datatypes: expected CHAR got LONG
Error In table: PLAN_TABLE Column:OTHER ORA-00932: inconsistent datatypes: expected CHAR got LONG
Table Name : Column Name
EMP : EMPNAME
EMP1 : EMPNAME
 
PL/SQL procedure successfully completed.
 
SQL> 
 
 

If you see above , it list the tables that were errored out due to column datatype LONG and the list of tables that contain the value 'BCD'

Just BUILD on this and create your code as per your requirement