creating data diccionaries 1

[Apprentice101]

I am doing data dictionaries for our small dept database. About 40 tables and 1000 variables. I will be doing this on Excel. The columns that I will be using are:

Name | Description | Var type | Null?| Label | Constrains

as you can see some of those are the output of the Describe command so to make my life easier, does anyone know how to put that into Excel? at least whatever is possible, the rest, I will have to type manualy unless someone has any suggestions?

by the way, I can borrow ER/Studio, Rapid SQL, DBArtisan or TOAD (in case someone knows how to do this with those tools.

thanks so much guys

 

[SantaMufasa]

Apprentice,

I have a few questions:

1) What does the "Name" column contain? (Table Name, Column Name, other?)
2) What does the "Description" column contain?
3) What does "Label" contain?
4) How much information does "Constraints" contain?...Constraint name, enforcement, et cetera?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

You may find almost anything you need (and even more!) from Oracle data dictionary views, thus I can't understand why you need it in Excell. Look at [DBA|ALL|USER]_[TABLES|TAB_COLUMNS|TAB_COMMENTS|COL_COMMENTS] views, the names are self-descriptive: prefix DBA means "overall database", ALL - "everything I may acess", USER - "only my own". E.g. USER_TAB_COMMENTS - comments on my own tables. Note that DBA_... views are not accessible by anybody.

Regards, Dima

http://seminihin.narod.ru

 

[Apprentice101]

thanks SEM, well that is exactly why I need to create my own cause I dont have access to the DBA views.

SantaMufasa,

1) What does the "Name" column contain? (Table Name, Column Name, other?)

"NAME" MEANS THE COLUMN NAME

2) What does the "Description" column contain?
"DESCRIPTION MEANS THE DESCRIPTION OF THE VARIABLE. FOR INSTANCE: VARIABLE 'GENDER' IS THE NAME, DESCRIPTION WILL BE: GENDER THAT THE EMPLOYEE CURRENTLY HAS. (NOT NECESSERALY BIOLOGICAL GENDER)

3) What does "Label" contain?

LABEL WILL CONTAIN THE LABEL ON THE APPLICATION FOR INSTANCE: VARIABLE LAST_NAME THE LABEL IN THE APPLICATION WILL BE EMPLOYEE LAST NAME

4) How much information does "Constraints" contain?...Constraint name, enforcement, et cetera?
CONSTRAINS ONLY NEEDS WHATEVER ORACLE MAY PROVIDE, I REALLY DONT KNOW WHAT IS AVAILABLE.

BY THE WAY, I was also thiking that maybe I could use the comments seccion for each variable to put all of this info in stead of creating it in excel. So now another question is: how do I retreive the commnets on my describe command?

thank you guys very much. and please feel free to suggest alternatives, SEM, what query do I run to see if I have access to the DBA views? if I state nice and clear I may bed the DBA to gime access to that query only.

thanks again!

 

[sem]

So how do you plan to populate your dictionary by objects that you don't even see???

Regards, Dima

http://seminihin.narod.ru

 

[Apprentice101]

What do you mean Sem? I dont know exactly what I can and cannot see.

i know I can run describe to any of my tables, ( the ones I own in my instance) that is why I asked you what other queries I can run to see what else is there. I was planning on using the info that the describe command gives you and the the rest I was going to add it manualy.

 

[sem]

Any information you may get from DESCRIBE command is accessible via ALL_... views, so you don't need to se DBA_... ones. Actually that command just queries ALL_TAB_COLUMNS behind the scenes.

Regards, Dima

http://seminihin.narod.ru

 

[Apprentice101]

ok,
what else can I retreive with the describe command?
and is there anyway to export those results out to a spreedsheet?

 

[sem]

You may spool results of your query from ALL_TAB_COLUMNS to a file and then load it directly to Excell.

Regards, Dima

http://seminihin.narod.ru

 

[Apprentice101]

GREAT!

I WAS ABLE TO RUN THE FOLLOWING QUERY:

select *
from ALL_TAB_COLUMNS
where owner='APPRENTICE'

NOW THE ISSUE IS: WHO CAN I DISPLAY THE COMMENTS OF EACH VARIABLE? THAT COLUMN DIDNT SHOW IN THAT QUERY.

THANKS!

 

[sem]

Read my first post carefully, everything is there

Regards, Dima

http://seminihin.narod.ru

 

[Apprentice101]

got it, I just found out that rapid sql can let you copy and paste the results as displayed.
thanks so much for your input.