declare
l_schema_name varchar2(30) := 'MY_SCHEMA';
l_table_name varchar2(30) := 'A_TABLE';
cursor c_cols is
select column_name
from all_tab_columns col
where col.table_name = l_table_name
and col.owner = l_schema_name
and col.column_name like 'COLUMN%';
begin
for r_cols in c_cols loop
execute immediate 'alter table '||l_schema_name||'.'||l_table_name||' drop column '||r_cols.column_name;
end loop;
end;
/
And here's one to generate a check constraint on all index colunns of a table:
declare
l_schema_name varchar2(30) := 'MY_SCHEMA';
l_table_name varchar2(30) := 'A_TABLE';
l_constraint_name_pfx varchar2(30) := 'XXX_ALIAS_CHK';
l_idx pls_integer := 1;
cursor c_cols is
select column_name
from all_tab_columns col
where col.table_name = l_table_name
and col.owner = l_schema_name
and col.column_name like 'IND_%';
begin
for r_col in c_col loop
execute immediate 'ALTER TABLE '||l_schema_name||'.'||l_table_name||' ADD CONSTRAINT '||l_constraint_name_pfx||l_idx||' CHECK ('||r_col.column_name||' in (''J'',''N''))ENABLE';
l_idx := l_idx+1;
end loop;
end;
/
