how do I check before adding a new column to a table if it’s exists or not ?
thanks in advance
Question
Share
Sign Up to our social questions and Answers to ask questions, answer people’s questions, and connect with other people.
Login to our social questions & Answers to ask questions, answer people’s questions & connect with other people.
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Please briefly explain why you feel this question should be reported.
Please briefly explain why you feel this answer should be reported.
Please briefly explain why you feel this user should be reported.
You can find the following view to access all metadata about the columns :
user_tab_cols; — For all tables owned by the user
all_tab_cols ; — For all tables accessible to the user
dba_tab_cols; — For all tables in the Database.
and lets consider you want to add new column only if doesn’t exists you can use this pl/sql to check and add new column
[code]DECLARE
v_column_exists number := 0;
BEGIN
Select count(*)
into v_column_exists
from user_tab_cols
where column_name = ‘ADD_COLUMN’
and table_name = ‘departments’;
if (v_column_exists = 0) then
execute immediate ‘alter table departments add (ADD_COLUMN NUMBER)’;
end if;
end;[/code]
hope this help.