Oracle SQL: How to drop columns that start with certain characters?-CodePudding

Folks, I have a table with 300 columns, out of which ~150 columns, that start with "OP_..." is not needed.

Would it be possible to write a query like,

Select * from TableA
drop columns start with OP_

in Oracle SQL/PL/SQL? Your comments are appreciated..

CodePudding user response：

You can try to run as one statement. Execute this select to generate the alter table statement, then execute that:

select to_clob('alter tableA drop (') || to_clob(listagg(column_name,',') ||')')
from user_tab_columns 
where table_name = 'TABLEA'
and column_name like '%OP_%'

If you want to do as individual statements, then you can execute a pl/sql block like this:

begin
  for rec in (select 'alter tableA drop (' || column_name ||')' drop_stmt
              from user_tab_columns
              where table_name = 'TABLEA'
              and column_name like '%OP_%') LOOP
     execute immediate  rec.drop_stmt
   END LOOP;
END;