10

I am trying to find how many occurrences of a particular value there are in a particular column in an Oracle database. The column is used in dozens of tables and I'm going to have to run the queries many times, so I don't want to query each table individually. I can get a list of the tables to search with something like:

Select table_name from all_tab_cols
   join all_tables using (table_name)
   where column_name = 'EmployeeId' and num_rows > 0

The next step is to iterate through that list of table names and output each table that contains a particular value in the EmployeeId column. For example, output might be something like:

**Table Name   Column_name   # Rows for EmployeeId = '123456'**
Table 1        EmployeeId                    1
Table 2        EmployeeId                   12
etc.

I'm not a developer and don't have experience using cursors in SQL scripts, so any help would be greatly appreciated.

cнŝdk
  • 31,391
  • 7
  • 56
  • 78
1earldog
  • 131
  • 1
  • 2
  • 7
  • Unrelated, but: the join should be on `owner` and `table_name`, not just `table_name` (alternatively use `user_tables` and `user_tab_columns` instead –  Apr 03 '18 at 16:55
  • Probably the column is named `'EMPLOYEEID'` and not `'EmployeeId'`. – William Robertson Apr 03 '18 at 17:58
  • It's actually an early 2000's vintage PeopleSoft database, and the column is 'EMPLID'. I just changed the name for readability. – 1earldog Apr 03 '18 at 19:34

1 Answers1

21

try using CURSOR FOR LOOP.

Probably it may look as shown below (not tried).

BEGIN
FOR item IN
(Select table_name,column_name,num_rows  from all_tab_cols
   join all_tables using (table_name)
   where column_name = 'EmployeeId' and num_rows > 0)
LOOP
DBMS_OUTPUT.PUT_LINE
(item.table_name || '    ' || item.column_name ||'    '||item.num_rows);
END LOOP;
END;
Pavan Chandaka
  • 11,671
  • 5
  • 26
  • 34
  • Your script runs successfully, but doesn't produce any visible output. I'm using Toad and I don't see any results on any of the Data Grid tabs. – 1earldog Apr 03 '18 at 19:31
  • 3
    You have to enable DBMS output to see the result. Please go through the link https://stackoverflow.com/questions/24959889/how-to-get-my-put-line-statement-to-display-in-toad – Pavan Chandaka Apr 03 '18 at 20:50
  • Perhaps you know how to use this technique if you want to select several rows inside loop? Or fill temporary table for each item? – FrenkyB May 12 '20 at 21:22