Data dictionary procedure issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Data dictionary procedure issue

Author

Topic

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2012-10-23 : 19:58:34

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 )
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE D1
LOGGING
STORAGE(PCTINCREASE 0
BUFFER_POOL DEFAULT
),
CONSTRAINT CSTR2 FOREIGN KEY (ATTR3)
REFERENCES X.DEF_MASTER(attr_y) ON DELETE CASCADE
)
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE DIAMOND
LOGGING
STORAGE(PCTINCREASE 0
BUFFER_POOL DEFAULT
)
ENABLE ROW MOVEMENT
;
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources