vicker313 tech blog

February 2, 2012

Oracle PL/SQL Script to Find Table(s) Based on Column Name and Value

Filed under: Oracle — Tags: , , — vicker313 @ 8:12 pm

Here is a simple oracle PL/SQL script, to find table(s) based on a column name and value that match with it. Just copy the scripts below and run it in SQL Plus. It will prompt user to enter the column name, the value you want to match and the schema, then it will show the table name and row count.

set serveroutput on
declare
col varchar2(20) := '&column_input';
val varchar2(20) := '&value_input';
cursor c is select table_name
from dba_tab_columns
where owner = '&owner_input'
and column_name = col;
cnt number;
begin
for r in c loop
cnt := 0;
execute immediate 'select count(*) from ' || r.table_name ||
' where ' || col || ' = ''' || val || '''' into cnt;
dbms_output.put_line(r.table_name || ': ' || cnt);
end loop;
end;
/
set serveroutput off

Create a free website or blog at WordPress.com.