CODEWITHSUNDEEP
sqloracle-database
Mike
Mike

Reputation: 2559

Finding tables where there is data

I am working on a system where there are 50/60 tables. Each has the same unique key (call it MEMBID for this example)

Is there a query I can run that will show me the names of all tables that have at least one row where the MEMBID exists?

Or do I need to cursor through the USER_TABLES table and then build a dynamic query to build up an "array"?

Many thanks

Mike

Upvotes: 1

Views: 61

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30845

I'd go for dynamic SQL - that's pretty straightforward:

declare
  l_cnt_membid number;
  l_cnt_overall number;
begin
  for cur in (select table_name from user_tab_cols where column_name = 'MEMBID')
    loop
      execute immediate 'select count(*), count(membid) from ' || cur.table_name 
         into l_cnt_overall, l_cnt_membid;
      dbms_output.put_line(cur.table_name || ', overall: ' || l_cnt_overall ||
         ', membid: ' || l_cnt_membid);
    end loop;
end;

EDIT:

If your table statistics are up-to-date, you can obtain this information from user_tab_cols directly:

select table_name,
  (case when num_distinct > 0 
   then 'YES' 
   else 'NO' end) has_nonnull_membid 
from user_tab_cols 
where column_name = 'MEMBID'

Upvotes: 1

Related Questions