sql developer formating for DB2

1) Use already set preferences  file :  

Use the suggested File to copy it into the local directory.

C:/....AppData\Roaming\SQL Developer\system4.1.3.20.78\o.sqldeveloper.12.2.0.20.78\product-preferences.xml

Copy this attached file and replace it in your user directory ( refer the above folder structure).

inside this xml file you can find the preferences called "Arul-SQL"


2) Otherwise, you can set this manually by the following way.

 

settings : After install the driver for db2

Go to tools

go to preferences

go to database

go to sql formatter

go to other vendors

select db2 formatter

choose profile( we can have different profiles for formatter) I have used with my name Arul

then click Edit

 

Find_string_inthe_schema

This particular code is used to find out the given value is present in which table :

create table table_row_count ( name varchar2(100));

declare
l_count number:=0;
v_sql   varchar2(4000);
v_string varchar2(100):='Sample101';
begin
for i in ( select * from user_tab_cols  where data_type in ('CHAR','VARCHAR2') and table_name not like '%BIN$%')
loop
v_sql := ' select distinct 1 from '||i.table_name  || ' where '||i.column_name||' like '
          ||''''||'%vinodcust/vinodcustuser3/outback21%'||'''';
  dbms_output.put_line ( v_sql);
begin
 execute immediate  v_sql into l_count;
 insert into table_row_count values ( i.table_name);
exception
when no_data_found then
 null;
 end;
end loop;
end;
/

select * from table_row_count;

drop table table_row_count;