Table Counts on a field accross the entire Database

Ever need to look for a value in a field across the entire DB? Have 10k+ Table that have your particular field? Here is a plsql package that I create to help in tracking where a person’s ID (emplid) shows up in the DB. Takes about 3 minutes to process 1 emplid.

 

Usage: select * from table(tablecounts_pkg.mbi_tablecounts('<<EMPLID>>') ) ;

 

 

CREATE OR REPLACE PACKAGE SYSADM.tablecounts_pkg

IS

     -- Looks for all PS tables (Joins on User_tables to make sure it is a table) and does a count on that table based on EMPLID.

   TYPE outrec_typ IS RECORD (

      var_table      VARCHAR2 (30),

      var_rowcount   NUMBER

   );

 

   TYPE outrecset IS TABLE OF outrec_typ;

 --Usage select * from table(tablecounts_pkg.tablecounts('1978614') ) ;

   FUNCTION tablecounts (emplid VARCHAR)

      RETURN outrecset PIPELINED;

    

END tablecounts_pkg;

/

 

CREATE OR REPLACE PACKAGE BODY SYSADM.tablecounts_pkg

IS

   FUNCTION tablecounts (emplid VARCHAR)

      RETURN outrecset PIPELINED

   IS

      out_rec           outrec_typ;

 

      CURSOR tablenames

      IS

         SELECT a.table_name

           FROM SYS.user_tab_cols a, user_tables b

          WHERE column_name = 'EMPLID' AND a.table_name LIKE 'PS%' AND a.table_name = b.table_name;

 

      table_row_count   NUMBER;

      stmt              VARCHAR (200);

   BEGIN

      FOR x IN tablenames

      LOOP

         stmt := 'select count(*) from ' || x.table_name || ' where emplid = ''' || emplid || '''';

 

         --DBMS_OUTPUT.put_line (stmt);

         EXECUTE IMMEDIATE stmt

                      INTO table_row_count;

 

         IF table_row_count > 0

         THEN

            out_rec.var_table := x.table_name;

            out_rec.var_rowcount := table_row_count;

            PIPE ROW (out_rec);

         END IF;

      END LOOP;

 

      RETURN;

   END;

END tablecounts_pkg;

/