Re: EASY? Drop a column in a table.

From: Glenn Nicholas <Glenn_at_wplace.demon.co.uk>
Date: Mon, 11 Jul 1994 13:41:38 +0000
Message-ID: <773934098snz_at_wplace.demon.co.uk>

You can use dynamic SQL to make this task a little bit easier.

Here is a utility which produces a script to back up the table and create a new copy. To rename or drop a column, you just edit the script in the appropriate places. You still need to edit it a bit (and no doubt you could soup it up to make avoid even this). But this gets the job done very quickly.

Note the script takes no account of sizing, and therefore the new table will be created with default extent sizes - it is only a development utility after all. You could upgrade it to include sizing information without too much trouble.

• Disclaimer: this utility could severely damage a database if used incorrectly or without care - but it can also be very helpful. I have used it for some years and have had no problems with it. Use it at your own risk, and back up your data before trying it out.
  • Start of script

REM
REM Tables : USER_TAB_COLUMNS, USER_IND_COLUMNS REM
set linesize 250
accept 1 char prompt 'Enter the name of the table to convert: ' spool conv.sql
set pagesize 0
set feedback off
set verify off
REM
REM Drop an old backup table if it exists. - note the assumption that these REM tables have the _OLD extension, so change this if it REM conflicts with objects in your database. REM
select 'drop table '||upper('&1')||'_OLD;' from dual; REM
REM Build the create statement in the form: REM create table x_new (col1) as select col1 from x; REM
select 'create table '||upper('&1')||'_NEW (' from dual; select column_name||','
  from user_tab_columns
 where table_name = upper('&1')
 order by column_id;
select ') as select ' from dual;
select column_name||','
  from user_tab_columns
 where table_name = upper('&1')
 order by column_id;
select 'from &1;' from dual;
REM
REM Rename the existing table to make an _OLD backup. Note this takes REM indexes with it. Then rename the _NEW table to become the REM actual table.
REM
select 'rename '||upper('&1')||' to '||upper('&1')||'_OLD;' from dual; select 'rename '||upper('&1')||'_NEW to '||upper('&1')||';' from dual; REM
REM Now index the actual table. Dropping indexes on the new table REM is not really required, but sometimes it is convenient to skip the REM backup phase in which case these statements are used. REM
select 'drop index '||index_name||';'
  from user_indexes
 where table_name = upper('&&1')
 order by index_name;
select 'create '||decode(ui.uniqueness,'UNIQUE','unique ','')||'index '||

        ui.index_name||' on '||ui.table_name||' ('   from user_indexes ui
 where table_name = upper('&&1')
 order by index_name;
select column_name||','
  from user_ind_columns uic
 where uic.table_name = upper('&&1')
order by index_name, column_position;
select ');' from dual;
spool off
REM
REM Provide some instructions on the final editing required. REM

select 'Created conversion file conv.sql' from dual;
select 'Edit the conv.sql script and: ' from dual;
select '  1. Remove the trailing , from the last line of create table' 

  from dual;
select ' 2. Remove the trailing , from the last line of each create index'   from dual;
select ' 3. If there are multiple indexes, add ); where appropriate'   from dual;
REM
set feedback on
REM
REM End of script.

• End of script

-- 
Glenn Nicholas, Concept             |   Internet: Glenn_at_wplace.demon.co.uk
Ph: +44 908 221313                  | Compuserve: 100070,3562

Received on Mon Jul 11 1994 - 15:41:38 CEST