Re: Need to determine if a column exists or not

Query the data dictionary view ALL_TAB_COLUMNS

SELECT 'x'
FROM all_tab_columns
WHERE table_name = <your table>
AND column_name = <column to look for>

Regards, Andy

news.integraonline.com wrote:

> I am in a situation where I need to determine is a column exists in a
> database or not.
>
> Does anyone know a way to do this.
>
>
> If you are asking the "Why would one need to do this!", that I have
> seen in
> response to others ?'s that are like this.
>
> I will explain.
>
> Overview: I have an application that executes scripts using the Oracle
> OLEDB
> provider, the application queries a database, and determines what changes
> need to be made to a customers database. It then executes a SQL script
> based
> on the determination made.
>
> Problem: Somewhere in the past a bad version of a script file was
> distributed to an undetermined amount of customers, this file did NOT
> add a
> column X to the database. So we are left with some customers that DO have
> column X and some that DON'T.
>
> We can't just drop column X for the ones that already have it, because
> they
> have data in the field. And creating temp tables seems like a pain.
>
> Resolution(hopefully): Find a way to do determine if the column
> exists... If
> it does not we need to add it.
> Example (not intended to be Oracle Syntax),
> If ColumnX.Exists Then
> Alter table TableName Add X
> End If
>
> If we can do this, everyone is happy. And we don't have some customers
> with,
> and some without.
>
>
Received on Thu Oct 17 2002 - 17:48:28 CDT