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
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;
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;
set serveroutput off


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at

%d bloggers like this: