How to find tables with two column names in a database-CodePudding

I am trying to find a table in a database that has two column names. If I try to search the DB for a table with one column name I am able to pull back a list of table names that has that column, but when I do it for 2 columns I pull back nothing.

Here is what I have been using:

select table_name from all_tab_columns
where column_name = 'COLUMN_1' and column_name = 'COLUMN_2'

CodePudding user response：

Use OR or IN.
Then grouped by the table_name, it should be having 2 columns.

MySql

SELECT table_name 
FROM information_schema.columns
WHERE column_name IN ('COLUMN_1', 'COLUMN_2') 
GROUP BY table_name
HAVING COUNT(column_name) = 2

Oracle Database

SELECT table_name 
FROM all_tab_columns
WHERE column_name IN ('COLUMN_1', 'COLUMN_2') 
GROUP BY table_name
HAVING COUNT(column_name) = 2

CodePudding user response：

You can use :

select table_name
  from all_tab_columns
 where column_name = 'COLUMN_1'
   and table_name in (select table_name
                        from all_tab_columns
                       where column_name = 'COLUMN_2')