SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data dictionary procedure issue


Data dictionary procedure issue

Author
Message
rockstar283
rockstar283
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 669
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84631 Visits: 41067
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
rockstar283
rockstar283
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 669
yes..that was oracle code..I have edited it..basic table creation script remains same..so posted here..I dont like oracle
OTF
OTF
Mr or Mrs. 500
Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)

Group: General Forum Members
Points: 500 Visits: 4128
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
vikingDBA
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 929
I'm confussed. Are you looking for an Oracle data dictionary creator, or a SQL Server one?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search