DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Column Exists

  1. #1
    Join Date
    Oct 2002
    Posts
    2
    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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);

  4. #4
    Join Date
    Oct 2002
    Posts
    2

    Column Exists

    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

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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]

  6. #6
    Join Date
    Jan 2003
    Location
    newton, ma, usa
    Posts
    15
    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

  7. #7
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    8
    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 !!
    Brahmaiah Koniki

  8. #8
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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!
    Cheers!
    OraKid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width