Building a Data Dictionary w Sybase

Hi All,

I'm trying to build a data dictionary for my Sybase tables. I found the following code which works great. But I also need the datatype. Can anyone point me to the correct location?

select      O.name as "Table"
,              C.name as "Column", C.length as "Length"
from        sysobjects O
,              syscolumns C
where     O.id = C.id
and         O.type = "U"             -- user tables only
order by  O.name, C.colid
vbplayerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

grant300Commented:
You can do things this way but there is a much easier and nicer tool for doing this stuff and it is free.

Find a copy of the Sybase Client for Windows installation CD and install the copy of PowerDesigner Physical Architect.  It is included for free on the CD and is an incredibly powerful tool.

You can reverse engineer your entire database including tables, indexes, views, defaults, user data types, and stored procedures.

You can then pretty up the diagram so you have a nice data model to use.

In addition, though it is hard to figure out how to use, there is a very powerful reporting tool that you can use to create summary and detailed reports in just about any form you want.  Pretty stuff too with control over fonts and formats and the columns displayed, etc.

You can also go in to the table definitions and add descriptions then drill to the fields and add descriptions there as well.  This makes the dictionary reports much more useful.

Believe me when I say that this will save you a boat load of time and effort and you will wind up with a very professional product when you are done.

Regards,
Bill
0
simongvCommented:
The datatype is the usertype field from syscolumns. To get a useful value you need to link to the systypes tables usertype field...

select      O.name as "Table",
            C.name as "Column",
            C.length as "Length",
            t.name as "Datatype"
from        sysobjects O,              
            syscolumns C,
            systypes t
where     O.id = C.id
and         O.type = "U"             -- user tables only
AND       C.usertype = t.usertype
order by  O.name, C.colid


Beware of the "type" field in each table, this is not unique in systypes and is the underlying storage type rather than the physical type.


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.