Create Table DDL from Oracle to MS-SQL

So you want to create some tables in Microsoft SQL based on tables that exist in Oracle? The Microsoft SQL Migration Assistant (MSSMA) can help, but it’s a bit tedious and if you have triggers on your tables, MSSMA will create rowid columns in SQL that don’t really exist in Oracle except as pseudo-columns used to indicate where data is stored in a file on disk which is subject to change. I will cover a few why’s and caveats and then get to the script.

Why in the world…?

First off, why in the world would you want to create tables in SQL that already exist in Oracle? There could be any number of reasons including application testing and replication. In both cases this script will give you the basic table, index and constraint structures. This script will not copy triggers or functions or procedures that exist in Oracle as most of those will require extensive rewriting in order to work on the SQL platform. I have used this script to prepare tables for replication from Oracle to SQL using SQL Replication. If you’re going to use this script as a starting point for replication, take out the referential (foreign key) constraints section at the end of the script. SQL replication does not copy data in a specific order and you may end up with numerous constraint violations if parent keys are not replicated before child data.

This script does use file groups in the table definition. The file group names are based on the tablespace names of the source tables in Oracle. So, in order for this to work, you’ll either need to take out the file group reference in the script or have the file groups already created in SQL. I do not have a script for automatically creating file groups based on tablespaces. Not yet.

Caution

Second, there are a few caveats. As mentioned above, this script will not copy triggers or functions or procedures. Most of these structures do not have a one-to-one translation from Oracle to SQL. If you use MSSMA to copy these structures, be aware that MSSMA may create a rowid column in an attempt to duplicate the Oracle rowid. Unless you’re using replication, your application will have to populate the rowid column. If you are using replication, be aware that the Oracle rowid is subject to change if you move the table to a different tablespace or reorganize the table and that may cause errors in replication.

On the subject of replication, this script includes a section to duplicate foreign keys. Replication does not require a parent key to exist before it copies child data. Therefore, you may see constraint violation errors and lost data during replication if you leave the foreign keys in place. If you intend to use replication, I recommend disabling the foreign key constraints during replication or that you exclude them entirely from the duplication by removing that section of the script.

If you intend to use this script as a starting point for testing the migration of your application data source from Oracle to SQL there are numerous considerations to be made after this starting point. For example, Oracle’s technique for outer joins (+) does not work in MS SQL. Additionally, you will need to consider tuning SQL since there are differences in the way Oracle and SQL use indexes and even the types of indexes available. For example, function-based indexes exist in Oracle and are useful for text searches, but do not exist in SQL.

As mentioned above, this script does create the tables in file groups based on the names of the tablespaces found in the Oracle source. You will need to have the file groups created in advance or edit the script to put the tables in the desired file group in SQL.

The final caveat is a summary of caveats, this is a starting point. It is not guaranteed to work in your environment.

The Script

You’ll find the script below. I have commented it throughout and included some directions for use. There are at least three items you need to change before this will run in your world include: the v_owner in the declare section, the list of tables to create in the v_tableList, the v_targetDB is the SQL database that MS-SQL uses to create tables.

How It Works

Basically, you enable the dbms_output window in Oracle SQL Developer. Then execute this script in SQL Developer as a user with SYSDBA privileges. You then copy the output and paste it into MSSQL Studio and execute it against the target database. You may need to adjust the size of your dbms_output. There is a field at the top of the dbms_output pane which allows you to size the buffer accordingly.

 

/**
The following script runs in Oracle and creates a script to run in MS-SQL.
The resulting script creates tables, keys and indexes in MS-SQL to reflect what is in Oracle.
One notable exception is the lack of function-based indexes which exist in Oracle but not MS-SQL.
This script should be run with sysdba privs.
This script does not create file groups in MS-SQL based on Oracle tablespaces.
This script does use filegroup names based on the Oracle tablespace names in the resulting create table scripts.
IMPORTANT!!!
The are a few items you need to change before this will run in your world include:
               the v_owner in the declare section
               the list of tables to create in the v_tableList
               the v_targetDB is the database that MS-SQL uses to create tables
http://mark.the-fennells.org
**/

DECLARE
  v_owner    VARCHAR2(32)   := 'ABL'; -- schema name
  v_count    NUMBER         := 1;
  v_default  VARCHAR2(4000) := NULL;
  v_nullable VARCHAR2(12)   := 'NULL';
  v_datatype VARCHAR2(64)   := 'varchar(256)';
  v_targetDB VARCHAR2(64)   := 'MY_DB_NAME';
type TableList
IS
  TABLE OF VARCHAR2(64);

  /** Put the list of tables you want to create in the TableList CSV string. **/
  v_tableList TableList := TableList('ABL_AGL_ALERTS');
BEGIN
  /** Setup a for loop to cycle through the tableList variable **/
  FOR v_table IN v_tableList.FIRST .. v_tablelist.LAST
  LOOP

    /** grab the tablespace name and make any necessary changes to get it into the correct file group on SQL. **/
    FOR tbl IN
    (SELECT owner,
      REPLACE(tablespace_name,'TBSP_','FLGRP_') AS file_group,
      table_name
    FROM dba_tables
    WHERE table_name = v_tableList(v_table)
    )

    /**
    The following is lifted almost directly from MS SQL Migration Assistant.
    This section drops existing tables and keys before trying to create them.
    **/
    LOOP
      dbms_output.put_line('USE '||v_targetdb||chr(10)||'GO'||chr(10));
      dbms_output.put_line('IF  EXISTS (SELECT * FROM sys.objects so JOIN sys.schemas sc ON so.schema_id = sc.schema_id WHERE so.name = N'''||tbl.table_name||'''  AND sc.name=N'''|| tbl.owner||'''  AND type in (N''U''))');
      dbms_output.put_line('BEGIN');
      dbms_output.put_line('  DECLARE @drop_statement nvarchar(500)');
      dbms_output.put_line('  DECLARE drop_cursor CURSOR FOR');
      dbms_output.put_line('    SELECT ''alter table ''+quotename(schema_name(ob.schema_id))+');
      dbms_output.put_line('    ''.''+quotename(object_name(ob.object_id))+ '' drop constraint '' + quotename(fk.name) ');
      dbms_output.put_line('    FROM sys.objects ob INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = ob.object_id');
      dbms_output.put_line('    WHERE fk.referenced_object_id = ');
      dbms_output.put_line('      (');
      dbms_output.put_line('        SELECT so.object_id ');
      dbms_output.put_line('        FROM sys.objects so JOIN sys.schemas sc');
      dbms_output.put_line('        ON so.schema_id = sc.schema_id');
      dbms_output.put_line('        WHERE so.name = N'''||tbl.table_name||'''  AND sc.name=N'''||tbl.owner||'''  AND type in (N''U'')');
      dbms_output.put_line('      )');
      dbms_output.put_line('  OPEN drop_cursor');
      dbms_output.put_line('  FETCH NEXT FROM drop_cursor');
      dbms_output.put_line('  INTO @drop_statement');
      dbms_output.put_line('  WHILE @@FETCH_STATUS = 0');
      dbms_output.put_line('  BEGIN');
      dbms_output.put_line('    EXEC (@drop_statement)');
      dbms_output.put_line('    FETCH NEXT FROM drop_cursor');
      dbms_output.put_line('    INTO @drop_statement');
      dbms_output.put_line('  END');
      dbms_output.put_line('  CLOSE drop_cursor');
      dbms_output.put_line('  DEALLOCATE drop_cursor');
      dbms_output.put_line(chr(10)||'  DROP TABLE ['||tbl.owner||'].['||tbl.table_name||']');
      dbms_output.put_line('END');
      dbms_output.put_line('GO');
      dbms_output.put_line('USE '||v_targetdb||chr(10)||'SET ANSI_NULLS ON'||chr(10)||'GO'||chr(10)||'SET QUOTED_IDENTIFIER ON'||chr(10)||'GO'||chr(10));
      dbms_output.put_line(chr(10)||'CREATE TABLE ['||tbl.owner||'].['||tbl.table_name||'] (');

      /**
      Next, we grab the table definition from Oracle and build the column definitions to meet the datatypes used in MSSQL.
      This is also a good place to make datatype revisions based on your application requirements.
      **/
      SELECT MAX(tc.column_id)
      INTO v_count
      FROM dba_tables t,
        dba_tab_columns tc
      WHERE t.owner    =tc.owner
      AND t.table_name = tc.table_name
      AND t.table_name = tbl.table_name;
      FOR col IN
      (SELECT rownum,
        t.owner,
        t.table_name,
        tc.column_name,
        tc.data_default,
        tc.nullable,
        tc.data_type,
        tc.char_length,
        tc.data_scale,
        tc.data_precision,
        tc.column_id
      FROM dba_tables t,
        dba_tab_columns tc
      WHERE t.owner    =tc.owner
      AND t.table_name = tc.table_name
      AND t.table_name = tbl.table_name
      ORDER BY t.owner,
        t.table_name,
        tc.column_id
      )
      LOOP
        -- check and set datatype with precison and scale and convert to
        -- mssql datatype equivalent
        IF col.data_type         = 'NUMBER' THEN
          IF col.data_precision IS NULL THEN
            v_datatype          := ' int ';
          ELSE
            v_datatype := ' numeric('||col.data_precision||','||col.data_scale||') ';
          END IF;
        elsif col.data_type             = 'FLOAT' THEN
          v_datatype                   := ' float ';
        elsif col.data_type             = 'RAW' THEN
          v_datatype                   := ' varbinary(2000) ';
        elsif col.data_type             = 'CLOB' THEN
          v_datatype                   := ' varchar(max) ';
        elsif col.data_type             = 'BLOB' THEN
          v_datatype                   := ' varbinary(max) ';
        elsif col.data_type             = 'NVARCHAR2' THEN
          v_datatype                   := ' nvarchar('||col.char_length||') ';
        elsif col.data_type             = 'DATE' THEN
          v_datatype                   := ' datetime2(7) ';
        elsif SUBSTR(col.data_type,1,9) = 'TIMESTAMP' THEN
          v_datatype                   := ' datetime2(7) ';
        elsif col.data_type             = 'VARCHAR' THEN
          IF col.char_length           <= 3 THEN
            v_datatype                 := ' char('||col.char_length||') ';
          ELSE
            v_datatype := ' varchar('||col.char_length||') ';
          END IF;
        elsif col.data_type   = 'VARCHAR2' THEN
          IF col.char_length <= 3 THEN
            v_datatype       := ' char('||col.char_length||') ';
          ELSE
            v_datatype := ' varchar('||col.char_length||') ';
          END IF;
        elsif col.data_type = 'CHAR' THEN
          v_datatype       := ' char('||col.char_length||') ';
        ELSE
          /** !! this is a WARNING datatype used to flag problems !! **/
          v_datatype := ' varchar(1234) ';
        END IF;
        -- check and set default value
        IF SUBSTR(col.data_default,1,7)     = 'SYSDATE' THEN
          v_default                        := ' DEFAULT sysdatetime() ';
        elsif SUBSTR(col.data_default,1,12) = 'CURRENT_DATE' THEN
          v_default                        := ' DEFAULT getDate() ';
        elsif col.data_default             IS NULL THEN
          v_default                        := '';
        ELSE
          v_default := ' DEFAULT '||trim(col.data_default);
        END IF;
        -- check and set nullability
        IF col.nullable = 'Y' THEN
          v_nullable   := ' NULL';
        ELSE
          v_nullable := ' NOT NULL';
        END IF;
        -- create the column definition and append a comma if it's not the last column in the table
        /** use this section to create standards for columns with the same names **/
        IF col.column_id     < v_count THEN
          IF col.column_name = 'LAST_UPDATED_BY' OR col.column_name='CREATED_BY' THEN
            dbms_output.put(chr(9)||rpad('['||col.column_name||']',32,' ')||'   '||'varchar(256)'||v_default||v_nullable||','||chr(10));
          ELSE
            dbms_output.put(chr(9)||rpad('['||col.column_name||']',32,' ')||'   '||v_datatype||v_default||v_nullable||','||chr(10));
          END IF;
        ELSE
          IF col.column_name = 'LAST_UPDATED_BY' OR col.column_name='CREATED_BY' THEN
            dbms_output.put(chr(9)||rpad('['||col.column_name||']',32,' ')||'   '||'varchar(256)'||v_default||v_nullable||chr(10));
          ELSE
            dbms_output.put(chr(9)||rpad('['||col.column_name||']',32,' ')||'   '||v_datatype||v_default||v_nullable||chr(10));
          END IF;
        END IF;
      END LOOP;
      /**
      Primary Key Constraints
      **/
      dbms_output.put_line(chr(10)||'/*  PRIMARY KEY CONSTRAINTS  */');
      BEGIN
        FOR con IN
        (SELECT owner,
          table_name,
          constraint_name
        FROM dba_constraints
        WHERE owner        = v_owner
        AND table_name     = v_tableList(v_table)
        AND constraint_type='P'
        )
        LOOP
          dbms_output.put_line(chr(10)||'ALTER TABLE ['||con.OWNER||'].['||con.TABLE_NAME||'] ADD CONSTRAINT ['||con.CONSTRAINT_NAME||'] PRIMARY KEY CLUSTERED (');
          SELECT MAX(position)
          INTO v_count
          FROM dba_cons_columns
          WHERE owner        =con.owner
          AND table_name     =con.table_name
          AND constraint_name=con.constraint_name;
          FOR concol IN
          (SELECT rownum,
            column_name,
            position
          FROM dba_cons_columns
          WHERE owner        =con.owner
          AND table_name     =con.table_name
          AND constraint_name=con.constraint_name
          ORDER BY position ASC
          )
          LOOP
            IF concol.rownum < v_count THEN               dbms_output.put('['||concol.column_name||'] ASC,');             ELSE               dbms_output.put('['||concol.column_name||'] ASC');             END IF;           END LOOP;           dbms_output.put_line(')'||chr(10)||'GO');         END LOOP;       END;       /**       Unique Constraints       **/       dbms_output.put_line(chr(10)||'/*  UNIQUE CONSTRAINTS  */');       BEGIN         FOR con IN         (SELECT owner,           table_name,           constraint_name         FROM dba_constraints         WHERE owner        =v_owner         AND table_name     = v_tableList(v_table)         AND constraint_type='U'         )         LOOP           SELECT MAX(position)           INTO v_count           FROM dba_cons_columns           WHERE owner        =con.owner           AND table_name     =con.table_name           AND constraint_name=con.constraint_name;           dbms_output.put_line('ALTER TABLE ['||con.OWNER||'].['||con.TABLE_NAME||'] ADD CONSTRAINT ['||con.CONSTRAINT_NAME||'] UNIQUE NONCLUSTERED (');           FOR concol IN           (SELECT rownum,             column_name,             position           FROM dba_cons_columns           WHERE owner        =con.owner           AND table_name     =con.table_name           AND constraint_name=con.constraint_name           ORDER BY position ASC           )           LOOP             IF v_count > concol.position THEN
              dbms_output.put('['||concol.column_name||'],'||chr(10));
            ELSE
              dbms_output.put('['||concol.column_name||']'||chr(10));
            END IF;
          END LOOP;
          dbms_output.put_line(')'||chr(10)||'GO');
        END LOOP;
      END;
      /**
      Check Constraints
      **/
      dbms_output.put_line(chr(10)||'/*  CHECK CONSTRAINTS  */');
      BEGIN
        FOR con IN
        (SELECT owner,
          table_name,
          constraint_name,
          search_condition
        FROM dba_constraints
        WHERE owner        =v_owner
        AND table_name     = v_tableList(v_table)
        AND constraint_type='C'
        )
        LOOP
          IF con.search_condition = 'IS NOT NULL' THEN
            dbms_output.put_line('ALTER TABLE ['||con.OWNER||'].['||con.TABLE_NAME||'] ADD CONSTRAINT ['||con.CONSTRAINT_NAME||'] CHECK ('||REPLACE(con.search_condition,'"')||');');
          END IF;
        END LOOP;
        dbms_output.put_line(chr(10)||'GO');
      END;
      /**
      Normal Indexes
      **/
      dbms_output.put_line(chr(10)||'/*  NORMAL INDEXES  */');
      BEGIN
        FOR ind IN
        (SELECT table_owner,
          table_name,
          index_name,
          REPLACE(tablespace_name,'TBSP_','FLGRP_') AS file_group
        FROM dba_indexes
        WHERE table_owner   =v_owner
        AND table_name      =v_tableList(v_table)
        AND ( index_type    = 'NORMAL'
        OR index_type       = 'DOMAIN' )
        AND index_name NOT IN
          (SELECT INDEX_NAME
          FROM dba_constraints
          WHERE owner         =v_owner
          AND table_name      = v_tableList(v_table)
          AND (constraint_type='P'
          OR constraint_type  ='U')
          )
        )
        LOOP
          SELECT MAX(column_position)
          INTO v_count
          FROM dba_ind_columns
          WHERE table_owner =ind.table_owner
          AND table_name    =ind.table_name
          AND index_name    =ind.index_name;
          dbms_output.put('CREATE NONCLUSTERED INDEX ['||ind.index_name||'] on  ['||ind.table_owner||'].['||ind.table_name||'] (');
          FOR indcol IN
          (SELECT column_name,
            column_position,
            descend
          FROM dba_ind_columns
          WHERE table_owner=ind.table_owner
          AND table_name   = ind.table_name
          AND index_name   = ind.index_name
          ORDER BY column_position
          )
          LOOP
            IF v_count > indcol.column_position THEN
              dbms_output.put('['||indcol.column_name||'] '||indcol.descend||',');
            ELSE
              dbms_output.put('['||indcol.column_name||'] '||indcol.descend);
            END IF;
          END LOOP;
          dbms_output.put_line(') ON ['||ind.file_group||'];');
        END LOOP;
        dbms_output.put_line(chr(10)||'GO');
      END;
    END LOOP;
    /**
    Referential Constraints
    Foreign Key constraints are created last in a separate loop using the v_TableList
    to ensure that primary keys are in place.
    **/
    dbms_output.put_line(chr(10)||'/*  REFERENTIAL CONSTRAINTS  */');
    FOR v_table IN v_tableList.FIRST .. v_tablelist.LAST
    LOOP
      FOR tbl IN
      (SELECT owner,
        REPLACE(tablespace_name,'TS_','FG_') AS file_group,
        table_name
      FROM dba_tables
      WHERE table_name = v_tableList(v_table)
      )
      LOOP
        BEGIN
          FOR con IN
          (SELECT owner,
            table_name,
            constraint_name,
            search_condition
          FROM dba_constraints
          WHERE owner        =v_owner
          AND table_name     = v_tableList(v_table)
          AND constraint_type='R'
          )
          LOOP
            FOR ref IN
            (SELECT ucc1.table_name constraint_table,
              ucc1.column_name constraint_column,
              ucc2.table_name reference_table,
              ucc2.column_name reference_column,
              ucc2.owner
            FROM dba_constraints uc,
              dba_cons_columns ucc1,
              dba_cons_columns ucc2
            WHERE uc.constraint_name = ucc1.constraint_name
            AND uc.r_constraint_name = ucc2.constraint_name
            AND uc.constraint_type   = 'R'
            AND uc.constraint_name   = con.constraint_name
            )
            LOOP
              dbms_output.put_line('ALTER TABLE ['||con.OWNER||'].['||con.TABLE_NAME||'] WITH CHECK ADD CONSTRAINT ['||con.CONSTRAINT_NAME||'] FOREIGN KEY (['||ref.constraint_column||']) REFERENCES ['||ref.owner||'].['||ref.reference_table||'] (['||ref.reference_column||'])');
            END LOOP;
            dbms_output.put_line('GO');
          END LOOP;
        END;
      END LOOP;
    END LOOP;
    /** End Referential Constraints **/
  END LOOP;
END;

Leave a Reply