Column Exists

I am trying to check if a column exists in a table. If the column does not exist then I want to alter the table so I can add it, if it does exists then I want to do nothing. Any help would be appreciated.

Richard

Are you doing this for an upgrade script? You can just try to add the column and if it fails then it already exists. When we add columns or Foriegn keys we add them one at a time in our sql script so that if they aren't already there then they get added.

to check if its there describe the table

desc

If it isnt there to add it

alter table add

e.g

alter table emp add tel_num varchar2(50);

This is going to be done in an automated install script, therefore I don't have the ability to check the table structure visually. This is how I'm envisioning it should be done.

Example:

IF NOT EXISTS (select column_name from table_name) THEN
ALTER TABLE ADD column_name
END IF;

I hope this makes sense

How about creating a script like this..

Code:

---

SELECT DISTINCT
'ALTER TABLE '||TABLE_NAME||' ADD (new_col_name new_data_type);'
FROM USER_TAB_COLUMNS
MINUS
SELECT DISTINCT
'ALTER TABLE '||TABLE_NAME||' ADD (new_col_name new_data_type);'
FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'new_col_name'

---

Sanjay

[Edited by SANJAY_G on 10-17-2002 at 10:16 PM]

I also want to do the same thing for adding column, changing datatype, adding table etc. Is their nice way to do this and check if it already exists in the datbase. This will be at the customer site so will not be able to do describe etc.

Thanks
Sonali

Best way is develop stored function ( You may bundle all these functions in a stored package)
which will return 1 if object exists or else return 0 (say).

Now with in the body of function use appropriate dictionary tables like ALL_TABLES, ALL_OBJECTS etc to get what ever you want.

Develop stored procedure which:

1. use the function developed above in the if logic.

2. if true then use EXECUTE IMMEDIATE to execute dynamically created string for all your DDL statements.

Good luck !!

DO the column name and their datatype goin to be an input paramater from and procedure???

-- TO ADD A NEW COLUMN. IF THE INPUT COLUMN_NAME DOESN'T EXIST.

DECLARE
P_TABLE_NAME VARCHAR2(30) := 'TEST';
P_INPUT_COL_NAME VARCHAR2(75) := 'COL1';
P_COL_DATA_TYPE VARCHAR2(30) := 'NUMBER(9)';
BEGIN

SELECT COUNT(COLUMN_NAME) INTO COL_EXIST_CNT FROM ALL_TAB_COULMNS
WHERE OWNER = 'JEGAN' AND TABLE_NAME = P_TABLE_NAME AND COULMN_NAME = P_INPUT_COL_NAME;

IF COL_EXIST_CNT = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ' || P_TABLE_NAME || ' ADD ' || P_INPUT_COL_NAME || P_COL_DATA_TYPE;
END IF;
END;

-- TO MODIFY A COLUMN. IF THE INPUT COLUMN'S DATATYPE DOESN'T MATCH WITH THE EXISTING ONE.

DECLARE
P_TABLE_NAME VARCHAR2(30) := 'TEST';
P_INPUT_COL_NAME VARCHAR2(30) := 'COL1';
P_COL_DATA_TYPE VARCHAR2(30) := 'NUMBER(9)';
BEGIN

SELECT COUNT(COLUMN_NAME) INTO COL_EXIST_CNT FROM ALL_TAB_COULMNS
WHERE OWNER = 'JEGAN' AND TABLE_NAME = P_TABLE_NAME AND COULMN_NAME = P_INPUT_COL_NAME AND DATA_TYPE != P_COL_DATA_TYPE;

IF COL_EXIST_CNT = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ' || P_TABLE_NAME || ' MODIFY ' || P_INPUT_COL_NAME || P_COL_DATA_TYPE;
END IF;
END;

hope this gives u some idea...

Cheers!