Counting Rows in Oracle in a Generic Fashion

As a DBA, every once in a while it is useful to have the ability of searching through all of the tables within a particular database to find a value. This can be a useful thing to do if you need to find all references to a particular record in a database table. Most likely, this kind of operation would need to be performed in order to safely delete a record from a database that was not using referential integrity. I know that I administer at least one legacy database which does not utilize referential integrity much at all. One major downfall to not using referential integrity is that records can be unsafely deleted, potentially leaving dangling references throughout the database.

One could easily write a block of code to search for a particular value throughout all tables in the database by iteration using views such as USER_TABLES. I have done so myself on many occasions. So much, that I grew tired of coding new blocks of code to search for particular values each time I needed to do so. I finally took a few minutes to write a generic stored procedure which will load into any Oracle database schema, and it has the ability to perform a full table search any particular value. The procedure takes two parameters, column name (or value name) and value. By invoking the procedure and passing these two values, the database will search through all tables within a particular schema and find those values within the column name you have given. The one caveat is that some databases use different column names throughout the database for the same identifiers. For example, I work with a database that calls an employee id number ID in one table, and EMP_ID in another. You must account for such discrepancies when using this procedure.

The code for this generic counting solution is as follows:

create or replace
procedure check_object_counts(column_name_val IN varchar2,
column_value IN varchar2) is
cursor table_cur is
select table_name
from user_tab_columns
where column_name = upper(column_name_val);

row_count number := 0;

begin
DBMS_OUTPUT.PUT_LINE('THE ' || upper(COLUMN_NAME_VAL) || ' IS REFERENCED IN THE FOLLOWING TABLES:');
for table_rec in table_cur LOOP
EXECUTE IMMEDIATE 'select count(*) from ' ||table_rec.table_name ||
' where ' || column_name_val || ' = :THIS_OBJECT'
INTO ROW_COUNT
USING column_value;

if row_count > 0 then
DBMS_OUTPUT.PUT_LINE(table_rec.table_name || ' - ' || row_count || ' times ');
end if;

row_count := 0;

end LOOP;

end;
/

As stated previously, this procedure can be loaded into any schema which has CREATE ANY PROCEDURE privileges. You invoke it as such from within SQLPlus:

@check_object_counts('EMP_ID','10037E');

** By the way, be sure to use SET SERVEROUTPUT ON prior to running the script so that you see the output!

It may not be a catch all solution, but it does the trick 98% of the time! Best to you and happy DBA-ing!