Oracle SQL only drop a column if a table exists-CodePudding

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.

CodePudding user response：

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;
/