SharpObject SharpObject - 3 months ago 8
SQL Question

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'

Answer

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