Oracle DBA: EMPLID Table Counts Script

Here’s a handy script that I put together today.  It finds all of the tables with the EMPLID field in it and prints a count of the rows that have a non-blank EMPLID value.

You can tweak it to work with other fields or find a certain value.

[sourcecode language=”sql”]
cursor tbls is select distinct a.recname
from psrecfieldall a, psrecdefn b
where a.recname = b.recname
and b.rectype = 0
and a.fieldname = ‘EMPLID’;
tbl tbls%ROWTYPE;
cnt number;
tablename varchar2(50);
sqltext varchar2(200);
open tbls;
fetch tbls into tbl;
exit when tbls%NOTFOUND;

select sqltablename into tablename from psrecdefn where recname = tbl.recname;
if tablename = ‘ ‘ then
tablename := ‘PS_’ || tbl.recname;
end if;
sqltext := ‘select count(*) from ‘ || tablename || ‘ where EMPLID <> ” ” ‘;
execute immediate sqltext into cnt;
if cnt > 0 then
dbms_output.put_line(tbl.recname || ‘ = ‘ || cnt);
end if;
end loop;
close tbls;
when others then
dbms_output.put_line(‘error with table: ‘ || tbl.recname);
dbms_output.put_line(‘    ‘ || SQLCODE || ‘: ‘ || SQLERRM);
dbms_output.put_line(‘    sql: ‘ || sqltext);


Any suggestions are welcome.

Leave a Comment

Your email address will not be published. Required fields are marked *