Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Alter Table if column doesn't exist

In a table, I want to check if a particular column exists or not. If the column does not exist, I want to alter the table and create that column.

I am using Oracle 11g.

like image 375
user968441 Avatar asked Jun 13 '12 06:06

user968441


People also ask

How do you check column not exists in table in SQL?

Now we use the below query to check the existence of a column. Query: IF COL_LENGTH('table_name','column_name') IS NOT NULL PRINT 'Column Exists'; ELSE PRINT 'Column does not Exists';

How do you check if a column exists in a table?

Another method to find if the column exists in a table is by using COL_LENGTH system function. This function returns the length of the column if it exists in the table. If not, it will return NULL.


1 Answers

Try this:

declare p_count NUMBER;

select count(1) int p_count
from ALL_TAB_COLUMNS 
where OWNER = '<SCHEMA_NAME>' 
and TABLE_NAME = '<TABLE_NAME>' 
and COLUMN_NAME = '<COLUMN_NAME>';

IF p_count = 0 THEN
    --add your column
END IF;

Eventually (depending on the rights) You can use user_tab_columns.

like image 136
Grzegorz W Avatar answered Sep 27 '22 15:09

Grzegorz W