Generate DROP TABLE Scripts - Oracle to SQL Server Migration

During an Oracle database migration to SQL Server, it may be helpful to have DROP TABLE scripts for tables in SQL Server database generated using the catalog views of the Oracle database.

Even if you do not need to transfer data from Oracle to SQL Server on a time-interval basis, you usually need to perform the migration several times.

Firstly you can do a test migration, or you may have configuration errors, or want to try different options and tools etc.

As a result, you have to clean up the SQL Server database before each new migration, and DROP TABLE scripts can be useful.

At the initial stage of migration, you may not have all tables in SQL Server yet, for example, due to transfer errors.

At the same time, the Oracle database contains accurate information on all migrated objects, so it is more reliable to use the Oracle catalog views.

The following Oracle SQL*Plus script generates DROP TABLE statements for each table in the current schema in SQL Server Transact-SQL:

Oracle:

  -- Suppress column headers
  SET HEADING OFF
  -- Do not print row count
  SET FEEDBACK OFF
 
  -- Specify SQL*Plus to output results to a file
  SPOOL 'c:\scripts\drop_table.sql'
 
  -- Specify SQL Server database (change db_name to your database name)
  SELECT 'USE db_name;' || CHR(10) || 
              'GO' || CHR(10)
  FROM dual; 
 
  SELECT 'PRINT ''Drop table ' || table_name || '''' || CHR(10) || 
              'GO' || CHR(10) ||
              'IF OBJECT_ID(''' || table_name || ''',''U'') IS NOT NULL' || CHR(10) ||
              CHR(9) || 'DROP TABLE ' || table_name || CHR(10) ||
              'GO' || CHR(10)
  FROM user_tables;
 
  -- Finish spooling to the file
  SPOOL OUT

• Oracle to SQL Server Migration
• Migration Convenience Scripts

Oracle 11g R2 Documentation

• SQL*Plus User Guide and Reference

Dmitry Tolpeko, dmtolpeko@sqlines.com - June 2012