ORACLE: SQL syntax to find table with two columns with names like ID, NUM

Question

My question is based on: Finding table with two column names If interested, please read the above as it covers much ground that I will not repeat here.

For the answer given, I commented as follows:

NOTE THAT You could replace the IN with = and an OR clause, but generalizing this to like may not work because the like could get more than 1 count per term: e.g.

SELECT OWNER, TABLE_NAME, count(DISTINCT COLUMN_NAME) as ourCount 
FROM all_tab_cols WHERE ( (column_name LIKE '%ID%') OR (COLUMN_NAME LIKE '%NUM%') ) 
GROUP BY OWNER, TABLE_NAME 
HAVING COUNT(DISTINCT column_name) >= 2 
ORDER BY OWNER, TABLE_NAME ; 

This code compiles and runs. However, it will not guarantee that the table has both a column with a name containing ID and a column with a name containing NUM, because there may be two or more columns with names like ID.

Is there a way to generalize the answer given in the above link for a like command. GOAL: Find tables that contain two column names, one like ID (or some string) and one like NUM (or some other string).

Also, after several answers came in, as "extra credit", I re-did an answer by Ahmed to use variables in Toad, so I've added a tag for Toad as well.

Drabble

To date, there are 3 answers, and all 3 agree, though it took a little work on my part to use them correctly. So I think the question is answered! Thanks to everyone. – Drabble

For all 3 answers to agree, one must use my separate answer that is essentially an edit of Littlefoot's answer. – Drabble

Answer 1

You may use conditional aggregation as the following:

SELECT OWNER, TABLE_NAME, COUNT(CASE WHEN COLUMN_NAME LIKE '%ID%' THEN COLUMN_NAME END) as ID_COUNT,
  COUNT(CASE WHEN COLUMN_NAME LIKE '%NUM%' THEN COLUMN_NAME END) NUM_COUNT
FROM all_tab_cols
GROUP BY OWNER, TABLE_NAME 
HAVING COUNT(CASE WHEN COLUMN_NAME LIKE '%ID%' THEN COLUMN_NAME END)>=1 AND
       COUNT(CASE WHEN COLUMN_NAME LIKE '%NUM%' THEN COLUMN_NAME END)>=1
ORDER BY OWNER, TABLE_NAME ;

See a demo.

If you want to select tables that contain two column names, one like ID and one like NUM, you may replace >=1 with =1 in the having clause.

Geisel

All answers work, and even agree [though it took work on my part to iron this out), I think this is the most elegant answer to date. – Drabble

Answer 2

You could do a UNION ALL and then a GroupBy with a Count on a subquery to determine the tables you want by separating your query into seperate result sets, 1 based on ID and the other based on NUM:

SELECT *
FROM
(
    SELECT OWNER, TABLE_NAME
    FROM all_tab_cols 
    WHERE column_name LIKE '%ID%'
    GROUP BY OWNER, TABLE_NAME
    UNION ALL
    SELECT OWNER, TABLE_NAME 
    FROM all_tab_cols 
    WHERE column_name LIKE '%NUM%'
    GROUP BY OWNER, TABLE_NAME
) x
GROUP BY x.OWNER, x.TABLE_NAME 
HAVING COUNT(x.TABLE_NAME) >= 2 
ORDER BY x.OWNER, x.TABLE_NAME ; 

Worldly

Answer 3

If I understood you correctly, you want to return tables that contain two (or more) columns whose names contain both ID and NUM (sub)strings.

My all_tab_cols CTE mimics that data dictionary view, just to illustrate the problem.

• EMP table contains 3 columns that have the ID (sub)string, but it should count as 1 (not 3); also, as that table doesn't contain any columns that have the NUM (sub)string in their name, the EMP table shouldn't be part of the result set
• DEP table contains one ID and one NUM column, so it should be returned

Therefore: the TEMP CTE counts number of ID and NUM columns (duplicates are ignored). The final query expects that table contains both columns.

Sample data:

SQL> with all_tab_cols (table_name, column_name) as
  2    (select 'EMP', 'ID_EMP' from dual union all
  3     select 'EMP', 'ID_MGR' from dual union all
  4     select 'EMP', 'SAL'    from dual union all
  5     select 'EMP', 'DID_ID'  from dual union all
  6     --
  7     select 'DEP', 'ID_DEP' from dual union all
  8     select 'DEP', 'DNUM'   from dual union all
  9     select 'DEP', 'LOC'    from dual
 10    ),

Query begins here:

 11  temp as
 12    (select table_name, column_name,
 13       sum(case when regexp_count(column_name, 'ID') = 0 then 0
 14                when regexp_count(column_name, 'ID') >= 1 then 1
 15           end) cnt_id,
 16       sum(case when regexp_count(column_name, 'NUM') = 0 then 0
 17                when regexp_count(column_name, 'NUM') >= 1 then 1
 18           end) cnt_num
 19     from all_tab_cols
 20     group by table_name, column_name
 21    )
 22  select table_name
 23  from temp
 24  group by table_name
 25  having sum(cnt_id) = sum(cnt_num)
 26     and sum(cnt_id) = 1;

TABLE_NAME
--------------------
DEP

SQL>

Gio

I made an edit (as yet unapproved) that does two things: i) Added comment about "WITH" ii) also changed the having clauses to both be ">= 1". The latter is a much more significant and important change, which I believe is the correct solution for the question that I (OP) asked. – Drabble

@Drabble Do not alter the functionality of answers by editing them. Add a new answer isntead, pointing out the flaws in the current answer, and how to resolve those. – Disposal

I made an answer; how do I gracefully withdraw the edit? – Drabble

I don't think you can; I just rejected it. – Gio

Answer 4

Make functions to re-use easely:

CREATE OR REPLACE FUNCTION get_user_tables_with_collist( i_collist IN VARCHAR2 )
RETURN SYS.ODCIVARCHAR2LIST
AS
    w_result    SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  w_re VARCHAR2(64) := '[^,;./+=*\.\?%[:space:]-]+' ;
BEGIN
  WITH collist(colname) AS (
    SELECT REGEXP_SUBSTR( UPPER(i_collist), w_re, 1, LEVEL ) FROM DUAL
    CONNECT BY REGEXP_SUBSTR( UPPER(i_collist), w_re, 1, LEVEL ) IS NOT NULL
  )
  SELECT table_name BULK COLLECT INTO w_result FROM (
    SELECT table_name, COUNT(column_name) AS n FROM user_tab_columns 
    WHERE EXISTS(
      SELECT 1 FROM collist 
      WHERE colname = column_name
    )
    GROUP BY table_name
  ) d
  WHERE d.n = (SELECT COUNT(*) FROM collist)
  ;
    RETURN w_result;
END ;
/

CREATE OR REPLACE FUNCTION get_all_tables_with_collist( i_owner IN VARCHAR2, i_collist IN VARCHAR2 )
RETURN SYS.ODCIVARCHAR2LIST
AS
    w_result    SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  w_re VARCHAR2(64) := '[^,;./+=*\.\?%[:space:]-]+' ;
BEGIN
    WITH collist(colname) AS (
      SELECT REGEXP_SUBSTR( UPPER(i_collist), w_re, 1, LEVEL ) FROM DUAL
      CONNECT BY REGEXP_SUBSTR( UPPER(i_collist), w_re, 1, LEVEL ) IS NOT NULL
    )
    SELECT table_name BULK COLLECT INTO w_result FROM (
      SELECT table_name, COUNT(column_name) AS n FROM all_tab_columns 
      WHERE EXISTS(
        SELECT 1 FROM collist 
        WHERE colname = column_name
      )
      AND owner = UPPER(i_owner) 
      GROUP BY table_name
    ) d
    WHERE d.n = (SELECT COUNT(*) FROM collist)
  ;
  
    RETURN w_result;
END ;
/

select * from get_all_tables_with_collist('sys', 'table_name;column_name') ;

ALL_COL_COMMENTS
ALL_COL_PENDING_STATS
ALL_COL_PRIVS
...

Palladous

Nice work. I already accepted a different answer, however. – Drabble

Answer 5

This is essentially an "edit" of Littlefoot's answer, that I believe makes things better. I give due credit, but I was asked to make this a separate answer, so I am doing so.

 11  temp as -- USE WITH IF not using the data part above
 12    (select table_name, column_name,
 13       sum(case when regexp_count(column_name, 'ID') = 0 then 0
 14                when regexp_count(column_name, 'ID') >= 1 then 1
 15           end) cnt_id,
 16       sum(case when regexp_count(column_name, 'NUM') = 0 then 0
 17                when regexp_count(column_name, 'NUM') >= 1 then 1
 18           end) cnt_num
 19     from all_tab_cols
 20     group by table_name, column_name
 21    )
 22  select table_name
 23  from temp
 24  group by table_name
 25  having sum(cnt_id) >= 1
 26     and sum(cnt_num) >= 1;

Drabble

Answer 6

This is a variant of the answer by Ahmed that uses conditional aggregation. I just updated it to use variables. This works in Toad. It may not work on other Oracle systems.

I think p3consulting gave a nice answer also, but the code below is shorter and somewhat easier to read (in my opinion).

For how I figured out how to add the variables in Toad, see answers by Alan in: How do I declare and use variables in PL/SQL like I do in T-SQL? Also, to use the script variables, run in Toad with "Run as script" otherwise, one would input variables, which, to me, is not very desirable.

var searchVal1 varchar2(20);
var searchVal2 varchar2(20);

exec :searchVal1 := '%ID%';
exec :searchVal2 := '%NUM%';

SELECT OWNER, TABLE_NAME 
 , COUNT(CASE WHEN COLUMN_NAME LIKE :searchVal1 THEN COLUMN_NAME END) as COUNT_1,
   COUNT(CASE WHEN COLUMN_NAME LIKE :searchVal2 THEN COLUMN_NAME END) as COUNT_2 
FROM all_tab_cols
GROUP BY OWNER, TABLE_NAME 
HAVING COUNT(CASE WHEN COLUMN_NAME LIKE :searchVal1 THEN COLUMN_NAME END)>=1 AND  
       COUNT(CASE WHEN COLUMN_NAME LIKE :searchVal2 THEN COLUMN_NAME END)>=1 
ORDER BY OWNER, TABLE_NAME ;

Drabble