-1

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?

1

2 Answers 2

0

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
2
  • 1
    There's no INFORMATION_SCHEMA in Oracle.
    – Littlefoot
    Jun 29 at 6:08
  • 1
    That's exactly what I needed. Thank you sir!
    – AnTiCoD
    Jun 29 at 6:18
0

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.