Find in what table I can find multiple collumns

Question

I am trying to write a query that will show me the tables that contains all the following columns: ProductGroup, ProductClass and ProductID.

But I am not very successful at it. Can somebody help me with that?

Answer 1

It depends on what you call "the entire db".

If it really is the database, then you'd query DBA_TAB_COLUMNS, connected as user who has access to that view (such as SYS, if you don't have any other):

SQL> connect sys@pdb1 as sysdba
Enter password:
Connected.
SQL> select owner, table_name from dba_tab_columns where column_name = 'EMPNO';
OWNER                          TABLE_NAME

SCOTT                          EMP
SCOTT                          TEST

If you want to check all tables you have access to, query ALL_TAB_COLUMNS:

SQL> connect scott@pdb1
Enter password:
Connected.
SQL> select owner, table_name from all_tab_columns where column_name = 'EMPNO';
OWNER                          TABLE_NAME

SCOTT                          EMP
SCOTT                          TEST

If you want to see tables that belong to your schema, then it is USER_TAB_COLUMNS (in some DBMS, they call it the "database"; in Oracle, that's just a "schema" - that's why I asked what the "database" means to you):

SQL> select table_name from user_tab_columns where column_name = 'EMPNO';
TABLE_NAME
EMP
TEST
SQL>

Answer 2

For Oracle:

SELECT TABLE_NAME
FROM ALL_IND_COLUMNS
WHERE COLUMN_NAME IN ('ProductGroup', 'ProductClass', 'ProductID')
GROUP BY TABLE_NAME
HAVING COUNT(*) = 3

For MS SQL and others:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('ProductGroup', 'ProductClass', 'ProductID')
GROUP BY TABLE_NAME
HAVING COUNT(*) = 3