Data dictionary procedure issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Data dictionary procedure issue

Data dictionary procedure issue

Rate Topic

Display Mode

Topic Options

Author

Message

rockstar283

Posted Tuesday, October 23, 2012 5:59 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 25, 2013 1:28 PM
Points: 84, Visits: 384

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

Jeff Moden

Posted Tuesday, October 23, 2012 8:09 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 32,889, Visits: 26,758

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1376296

rockstar283

Posted Tuesday, October 23, 2012 9:49 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 25, 2013 1:28 PM
Points: 84, Visits: 384

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

Post #1376312

OTF

Posted Wednesday, October 24, 2012 10:05 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 254, Visits: 3,711

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 Smile

Post #1376570

vikingDBA

Posted Thursday, October 25, 2012 9:18 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:19 AM
Points: 189, Visits: 863

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

Post #1377071

« Prev Topic | Next Topic »

Permissions