Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data dictionary procedure issue Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2012 5:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:42 PM
Points: 114, Visits: 529
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
Post #1376286
Posted Tuesday, October 23, 2012 8:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 35,262, Visits: 31,745
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1376296
Posted Tuesday, October 23, 2012 9:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:42 PM
Points: 114, Visits: 529
yes..that was oracle code..I have edited it..basic table creation script remains same..so posted here..I dont like oracle
Post #1376312
Posted Wednesday, October 24, 2012 10:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
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
Post #1376570
Posted Thursday, October 25, 2012 9:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:35 PM
Points: 191, Visits: 898
I'm confussed. Are you looking for an Oracle data dictionary creator, or a SQL Server one?
Post #1377071
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse