Re: re-create tables from Data Dictionary

From: <alacy_at_hayes.com>
Date: 4 Jun 93 08:01:38 EDT
Message-ID: <7387.2c0f0162_at_hayes.com>

In article <C82BB1.D58_at_umassd.edu>, benu_at_cis.umassd.edu (David Hassel) writes:
> Has anyone already written a program (C or perl maybe) to create
> an sql procedure to re-create a table from an existing table using
> all constraints and grants? I've started writing one but maybe some one
> has already done this sort of thing.
>
> Dave Hassel
>
> benu_at_cis.umassd.edu

I wrote an SQL*Plus script to do this. The following is from our internal Hint "Book"

X-NEWS: hayes hayes.oracle: 38
Path: hayes!alacy
From: alacy_at_hayes.com
Newsgroups: hayes.oracle
Subject: SQL*Plus Script to generate CREATE TABLE Message-ID: <7038.2bb2bf6d_at_hayes.com> Date: 26 Mar 93 08:31:41 EDT
Organization: Hayes Microcomputer Products, Norcross, GA Lines: 107

I have written a SQL*Plus script which will produce a create table script for any table in the Dictionary. It asks for the table name to create, and then writes the script. The script name will be crt_TABLE.sql where TABLE is the table name. Note: if you run this script on a PC the name of the script will be truncated to 8 charaters.

The script follows:

/************************************************************************/
/*  Name:     GENCRT.SQL                                                */
/*  Date:     26-Oct-92                                                 */
/*  Author:   A. Lacy                                                   */
/*  Purpose:  To read the Oracle Dictionary and produce a script        */
/*            which can be used to recreate a table.                    */
/*                                                                      */
/************************************************************************/

SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET LINESIZE 120
COL DUMMY1 NOPRINT FORMAT A1
COL DUMMY2 NOPRINT FORMAT A1
COL TABLE_NAME NOPRINT FORMAT A1
COL COMMAND WORD FORMAT A75
accept table_name char prompt 'Enter Table name: ' SPOOL crt_&&table_name..sql
/* GEN CREATE TABLE LINE */
SELECT ' CREATE TABLE ' || TABLE_NAME || '(' COMMAND,         1 DUMMY1, TABLE_NAME, 0 DUMMY2
 FROM USER_TABLES WHERE TABLE_NAME LIKE UPPER('&&TABLE_NAME') UNION
/* COLUMN DEFINITIONS */
SELECT

        DECODE(COLUMN_ID,1,' ',' ,') || COLUMN_NAME || ' ' || DATA_TYPE ||
        DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'CHAR','('||DATA_LENGTH||')',
               DECODE(DATA_PRECISION,NULL,NULL,
                      '('||DATA_PRECISION||','||DATA_SCALE||')')) || ' ' ||
        DECODE(NULLABLE,'Y',NULL,'NOT NULL') COMMAND,
       2 DUMMY1, TABLE_NAME, COLUMN_ID DUMMY2

 FROM USER_TAB_COLUMNS
 WHERE USER_TAB_COLUMNS.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES

                                        WHERE TABLE_NAME LIKE 

UPPER('&&TABLE_NAME'))
UNION
/* IF PRIMARY KEY */
SELECT ', PRIMARY KEY(' COMMAND, 3 DUMMY1, TABLE_NAME, 0 DUMMY2  FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P' AND
   USER_CONSTRAINTS.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES

                                       WHERE TABLE_NAME LIKE 

UPPER('&&TABLE_NAME'))
UNION
/* THE COLUMNS IN THE PRIMARY KEY */
SELECT

        DECODE(CC.POSITION,1,' ',' ,') || CC.COLUMN_NAME COMMAND,
        4 DUMMY1, CO.TABLE_NAME, POSITION DUMMY2

FROM USER_CONSTRAINTS CO, USER_CONS_COLUMNS CC WHERE

     CO.TABLE_NAME=CC.TABLE_NAME
 AND CO.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
 AND CO.CONSTRAINT_TYPE = 'P'
 AND CO.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES
                                       WHERE TABLE_NAME LIKE 

UPPER('&&TABLE_NAME'))
UNION
/* IF PRIMARY KEY CLAUSE THE CREATE ) */ SELECT ') CONSTRAINT ' || CONSTRAINT_NAME COMMAND,  5 DUMMY1, TABLE_NAME, 0 DUMMY2
 FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P' AND
   USER_CONSTRAINTS.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES

                                       WHERE TABLE_NAME LIKE 

UPPER('&&TABLE_NAME'))
UNION
/* END PARENTHESIS FOR LIST OF COLUMN DEFS */ SELECT ' )' COMMAND, 6 DUMMY1, TABLE_NAME, 0 DUMMY2  FROM USER_TAB_COLUMNS
 WHERE USER_TAB_COLUMNS.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES

                                       WHERE TABLE_NAME LIKE 

UPPER('&&TABLE_NAME'))
UNION

SELECT ' PCTFREE  ' || PCT_FREE ||
       ' PCTUSED  ' || PCT_USED ||
       ' INITRANS ' || INI_TRANS ||
       ' MAXTRANS ' || MAX_TRANS ||
       ' TABLESPACE ' || TABLESPACE_NAME ||
       ' STORAGE (INITIAL     ' || INITIAL_EXTENT ||
                ' NEXT        ' || NEXT_EXTENT ||
                ' MAXEXTENTS  ' || MAX_EXTENTS ||
                ' MINEXTENTS  ' || MIN_EXTENTS ||
                ' PCTINCREASE ' || PCT_INCREASE ||
                ');' COMMAND,
        7 DUMMY1, TABLE_NAME, 0 DUMMY2

 FROM USER_TABLES WHERE TABLE_NAME LIKE UPPER('&&TABLE_NAME')  ORDER BY 3,2,4;
SPOOL OUT
SET HEADING ON
SET PAGESIZE 20
SET TERMOUT ON
SET FEEDBACK ON

-- 
Allen Lacy, Principal Analyst | internet:  alacy_at_hayes.com              
(404) 840-9200 (x.2131)       | uucp:      uunet!hayes!alacy     
Hayes Microcomputer Products  | U.S.Mail:  p.o.box 105203, Atlanta, GA 30348

Received on Fri Jun 04 1993 - 14:01:38 CEST