Oracle SQL only drop a column if a table exists

Question

For Microsoft SQL Server I have following statement to only drop a column if the table exist.

IF EXISTS(SELECT 1 
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME = 'TEST_TABLE')
ALTER TABLE TEST_TABLE DROP COLUMN LEGACY_VALUE
GO

I was wondering if there was a related IF-EXISTS mechanism is present in Oracle.

Littlefoot · Accepted Answer · 2022-07-28 17:16:40Z

1

All the metadata about the columns in Oracle Database is accessible using one of the following views.

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.

So, if you are looking for a column that exists and want to drop it, your code may look something like this ( see below).

Since this appears to be a one time task, is the effort really worth it?


DECLARE
  v_column_exists number := 0;  
BEGIN
  Select count(*) into v_column_exists
    from user_tab_cols
    where upper(column_name) = 'LEGACY_VALUE''
      and upper(table_name) = 'TEST_TABLE';
      --and owner = 'SCOTT --*might be required if you are using all/dba views

  if (v_column_exists = 1) then
      execute immediate 'alter table test_table drop column legacy_value';
  end if;
end;
/

edited Jul 28, 2022 at 17:16

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

answered Jul 28, 2022 at 16:04

Pugzly

9441 gold badge10 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pugzly Over a year ago

You may want to consider watching what Connor McDonald from Oracle has to say about dropping columns youtu.be/vOeTT1Uwyn4

Collectives™ on Stack Overflow

Oracle SQL only drop a column if a table exists

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related