Find in what table I can find multiple collumns

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Method 2

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 [email protected] 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 [email protected]
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>

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply