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;
/
