Data dictionary procedure issue

Question

Data dictionary procedure issue

rockstar283

SSCrazy

Points: 2704
More actions
October 23, 2012 at 5:59 pm

#269959

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

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

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

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

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)

rockstar283 SSCrazy Points: 2704 More actions · Answer 2

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

OTF SSCertifiable Points: 6104 More actions · Answer 3

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 🙂

vikingDBA SSCertifiable Points: 5356 More actions · Answer 4

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