Re: view create table statements

From: Mark Cudworth <cudworth_at_removed.to.avoid.spam.invalid>
Date: Fri, 15 Mar 2002 18:21:54 -0000
Message-ID: <u94eu222l5n96a@corp.supernews.com>

Bjoern Henning <b.henning_at_safe4net.com> wrote:

> Is there a way of viewing the create table,view.. of an existing
> oracle database? I don't want to use a tool, just generating a file
> would be perfect. I need this for transforming a complex ora-db into
> mysql.

All of the information you need is in the system views. I wrote a Perl program a while ago that does just this. Invoking it (for a table named "tracks") as:

    dumpTable.pl --noData user/pass tracks

wiil yield (I apologize in advance for the lines longer than 80 characters.):

/*----------------------------------------------------------------------------*/


/*                                                                            */

/* Table: USER.TRACKS                                                         */

/* Account: user                                                              */

/* Database: prod                                                             */

/*                                                                            */

/* Dumped on 03/15/2002 at 13:08:17                                           */

/*                                                                            */

/*----------------------------------------------------------------------------*/



/* shut off pl/sql scanning so it won't mess with ampersand characters in     */

/* text fields                                                                */

set scan off

/* create statement for table TRACKS */

create table TRACKS (

    ALBUM_ID                       VARCHAR2(13) NOT NULL,
    SIDE                           NUMBER NOT NULL,
    TRACK                          NUMBER NOT NULL,
    TITLE                          VARCHAR2(255),
    TIME                           NUMBER,
    ARTIST_LAST                    VARCHAR2(40),
    ARTIST_FIRST                   VARCHAR2(20),

    constraint PK_TRACKS primary key (ALBUM_ID, SIDE, TRACK),     constraint FK_TRACKS_ALBUMS foreign key (ALBUM_ID) references ALBUMS(ALBUM_ID));

/* set up privileges for table TRACKS */

grant SELECT on TRACKS to PUBLIC;

/* add a comment to the table */

comment on table TRACKS is 'Information about individual tracks on albums listed in the albums table';

/* add comments to columns in table TRACKS */

comment on column TRACKS.ALBUM_ID is 'The album_id as found in the albums table.';
comment on column TRACKS.SIDE is 'Ths side of the album on which this tracks is found.';
comment on column TRACKS.TRACK is 'The track number for the SIDE which identifies this piece.';
comment on column TRACKS.TITLE is 'The title(s) of the piece(s) on this track.';
comment on column TRACKS.TIME is 'The play time for this track, in seconds.';
comment on column TRACKS.ARTIST_LAST is 'The last name of the artist for this track.  This is the band, group or orchestra name for artists that are not individuals.';
comment on column TRACKS.ARTIST_FIRST is 'The first name of the artist for this track.  This column is NULL for artists that are not individuals.';

So, as I said before, all the information you need to produce the create scripts is stored in the system views.

Mark Received on Fri Mar 15 2002 - 12:21:54 CST