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.
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.