CODEWITHSUNDEEP
sqloracle-databaseexists
SharpObject
SharpObject

Reputation: 607

How to check which columns exist in SQL

So I have a list of potential columns, and I know I can check if a certain column exists using.

SELECT null
  FROM user_tab_columns
  WHERE table_name = 'MYTABLE' and column_name = 'MYCOLUMN'

How do I expand this to test multiple columns? i.e. Not sure what the exact syntax would be.

SELECT null, null, null
  FROM user_tab_columns
  WHERE table_name = 'MYTABLE' and column_name = 'MYCOLUMN' 
  or column_name = 'MYCOLUMN1' or column_name = 'MYCOLUMN2'

Upvotes: 1

Views: 986

Answers (3)

sql_dummy
sql_dummy

Reputation: 745

Now you got the right answer,coming to Your query it would execute something like following, as AND has more precedence than OR in Oracle SQL. 

SELECT null, null, null
  FROM user_tab_columns
  WHERE (table_name = 'MYTABLE' and column_name = 'MYCOLUMN')    <-- MYCOLUMN in MYTABLE
  or column_name = 'MYCOLUMN1'                                   <-- MYCOLUMN1 in ANYTABLE
  or column_name = 'MYCOLUMN2'                                   <-- MYCOLUMN2 in ANYTABLE

Upvotes: 0

user5683823
user5683823

Reputation:

Here I created a table with a single column, column_name. I test the existence of column_name in the table EMP in the SCOTT schema. I assume all table, column and schema names are already upper-case (since all catalog string values are upper-case), otherwise you will need case-insensitive comparisons. This example assumes the current user has access to the SCOTT schema (a standard schema found on most Oracle installations), and I use the table ALL_TAB_COLUMNS instead of USER_TAB_COLUMNS (which only looks at the current user's tables).

with
     potential_columns ( column_name ) as (
       select 'EMPNO'   from dual union all
       select 'NAME'    from dual union all
       select 'MANAGER' from dual union all
       select 'DEPTNO'  from dual
     )
select p.column_name, 
       case when t.column_name is null then 'FALSE' else 'TRUE' end 
          as column_exists_in_table
from potential_columns p left outer join 
     (select column_name from all_tab_columns 
        where owner = 'SCOTT' and table_name = 'EMP') t
on p.column_name = t.column_name
;

COLUMN_NAME COLUMN_EXISTS_IN_TABLE
----------- ----------------------
EMPNO       TRUE 
DEPTNO      TRUE 
MANAGER     FALSE
NAME        FALSE

Upvotes: 1

Gaston Flores
Gaston Flores

Reputation: 2467

Maybe this code will be useful (I'm sure there must be a better solution):

select count(table_name) from all_tab_columns where column_name = 'YOUR_COLUMN';

For more than one colunm try this:

select count(table_name) from all_tab_columns where column_name in ( 'YOUR_COLUMN_1','YOUR_COLUMN_2')

This returns 0 if table not exists.

I hope will be useful for you

Upvotes: 1

Related Questions