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

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: