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.

declare
   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);
begin
   open tbls;
   loop
     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;
exception
   when others then
      dbms_output.put_line('error with table: ' || tbl.recname);
      dbms_output.put_line('    ' || SQLCODE || ': ' || SQLERRM);
      dbms_output.put_line('    sql: ' || sqltext);
end;
/

 

Any suggestions are welcome.