Wednesday 30 March 2016

Auto DDL: delete obsolete columns from table

A quick one. In the past I used to generate ddl based on queries, like the following. But I find myself to re-invent them again. So to have it saved for my offspring: here's one on deleting obsolete columns as generated on importing an excel sheet in SQLDeveloper:

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

No comments :