Data dictionary procedure issue

  • Hi..I have lots of lots of (around 1000) tables for which I need to create a data dictionary..The create table structure is as follows:

    CREATE TABLE x.def_deta(

    attr1 NUMBER NOT NULL,

    attr2 VARCHAR2(30),

    attr3 DATE,

    attr4 DATE,

    attr5 VARCHAR2(8),

    attr6 NUMBER NOT NULL DEFAULT 0,

    CONSTRAINT cstr1 PRIMARY KEY (attr1, attr2 ),

    CONSTRAINT CSTR2 FOREIGN KEY (ATTR3)

    REFERENCES X.DEF_MASTER(attr_y) ON DELETE CASCADE

    )

    From structures like this, I need create a data dictionary tabulating all the fields and other info like below:

    table_name attribute_name

    data_type data_length

    isnull PK

    FK REFERENCING_TABLE

    REFERENCING_ATTRIBUTE DEFAULT

    def_deta attr1

    NUMBER NOT NULL

    Y

    def_deta attr2 VARCHAR2 30 Y

    def_deta attr3

    DATE Y DEF_MASTER attr_y

    def_deta attr4

    DATE

    def_deta attr5

    VARCHAR2 8

    def_deta attr6

    NUMBER NOT NULL

    0

    Can anyone please help me to create a procedure to which I can simply feed the table structure and it will tabulate the dictionary as above..

    This will help me a lot and save a tonne of time..Thanks in advance

  • This looks like Oracle code. You'd be much better off hitting up on an Oracle forum.

    As a side bar, if anyone asked me for such a data dictionary on 1,000s of tables in SQL Server, I'd point them to learning how to right click on a database and learning how to use the "Generate Scripts" task. I worked with Oracle many years ago and a similar thing exists there. I just don't remember how to do it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • yes..that was oracle code..I have edited it..basic table creation script remains same..so posted here..I dont like oracle

  • rockstar283 (10/23/2012)


    yes..that was oracle code..I have edited it..basic table creation script remains same..so posted here..I dont like oracle

    There's nothing wrong with Oracle... It's an RDBMS as any other 🙂

  • I'm confussed. Are you looking for an Oracle data dictionary creator, or a SQL Server one?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply