• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2403
  • Last Modified:

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
1 Solution
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.

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now