Select from all_tab_columns where table has > 0 rows

Question

I need to search in a large DB a table that matches with a column name, but this table must have more than 0 rows.

Here is the query by the way:

SELECT * FROM all_tab_columns WHERE column_name LIKE '%ID_SUPPORT%';

Answer 1

You could use single query to filter names and get actual number of rows:

SELECT owner, table_name, cnt              
FROM all_tab_columns, XMLTABLE('/ROWSET/ROW' passing 
             (dbms_xmlgen.getxmltype(REPLACE(REPLACE(
             'select COUNT(*) AS cnt from <owner>.<table_name>', '<owner>', owner)
             , '<table_name>', table_name))) COLUMNS cnt INT)
WHERE column_name LIKE '%ID_SUPPORT%' AND cnt > 0;

DBFiddle Demo

---

Any chance this can be expanded/tweaked to yield the values of the first few rows for all tables?

Yes, by flattening row using JSON_ARRAYAGG(JSON_OBJECT(*)) Oracle 19c:

-- generic approach Oracle 19c
SELECT owner, table_name, cnt, example             
FROM all_tab_columns, XMLTABLE('/ROWSET/ROW' passing 
                 (dbms_xmlgen.getxmltype(REPLACE(REPLACE(
                  'select COUNT(*) AS cnt,
                          MAX((SELECT JSON_ARRAYAGG(JSON_OBJECT(*)) 
                               FROM <owner>.<table_name> 
                               WHERE rownum < 10)  -- taking up to 10 rows as example
                              ) as example 
                          from <owner>.<table_name>', '<owner>', owner)
                 , '<table_name>', table_name))) 
                 COLUMNS cnt INT
                        , example VARCHAR2(1000))
WHERE column_name LIKE '%ID_SUPPORT%'
      AND cnt > 0;

Demo contains hardcoded column list inside JSON_OBJECT. Oracle 19c and JSON_OBJECT(*) would allow any column list per table.

db<>fiddle demo

How it works:

1. find all tables that have column named '%ID_SUPPORT'
2. run query per table using dbms_xml_gen.getxmltype
3. in sub query count the rows, flatten few rows an example to JSON
4. return rows that have at least one record table

Answer 2

One way:

SELECT * FROM all_tables WHERE num_rows > 0
  AND table_name in (SELECT table_name FROM all_tab_columns WHERE column_name LIKE '%ID_SUPPORT%')

Answer 3

If your DB is periodically analyzed the direct way is to use the following SQL :

SELECT *
  FROM all_tables t 
 WHERE t.table_name LIKE '%ID_SUPPORT%'
   and t.num_rows > 0;

More precise way to determine is using the following :

declare
  v_val pls_integer := 0;
begin
  for c in (
            SELECT *
              FROM all_tables t
             WHERE t.table_name LIKE '%ID_SUPPORT%'
           ) 
  loop  

    execute immediate 'select count(1) from '||c.owner||'.'||c.table_name into v_val;

    if v_val > 0 then
     dbms_output.put_line('Table Name : '||c.table_name||' with '||v_val||' rows ');
    end if;

  end loop;  
end;

I'm confused with the word matches. If you mean column, but not table, you may use the following routine to get the desired tables with columns whose names are like ID_SUPPORT :

declare
  v_val pls_integer := 0;
begin
  for c in (
            SELECT t.*
              FROM all_tab_columns c
              JOIN all_tables t on ( c.table_name = t.table_name )
             WHERE c.column_name LIKE '%ID_SUPPORT%'
           ) 
  loop  

    execute immediate 'select count(1) from '||c.owner||'.'||c.table_name into v_val;

    if v_val > 0 then
     dbms_output.put_line('Table Name : '||c.table_name||' with '||v_val||' rows ');
    end if;

  end loop;  
end;