SQL Anywhere question

[MarkRCC]

Hi.

I hope this is the right forum to ask this question. If not, please direct me to the proper one.

I have a SQL database and would like to have a listing of all the tables in this database as well as the linking relationships of the tables.

I'm running SQL Adaptive Server Anywhere v 6.0.

I can retrieve a list of the tables in ISQL using the following command: Select * from sys.syscatalog

The output shows all the tables and, among other info, whether there is a primary key defined (yes/no) but what I would like is to dump not only a listing of all tables but also the field in each table that is a primary/foreign key and, if possible, the relationship each table has with all other tables.

Is this possible?? Am I making any sense whatsoever?

Basically I'm creating Crystal Report Writer reports and need to know the table relationships in my database. The problem is that this database has approximately 500 tables with thousands of fields.

Many TIAs for any help, suggestions, or even a "HUH???"

Mark

 

[sybaseguru]

Dear friend,

I do not know much about sql anywhere but I assume that it is very similar to sybase sql server.

First, it appears to me that you want a data model of the database you are working on for your reporting. That is you want an Entity-Relationship-Model (ERM). If you have sybase open client installed, there is a programme called "powerDesigner" under "sybase" on your PC. You can use powerDesigner to reverse engineer the database (select file and then reverse engineer). This uses ODBC drivers and should be easy for you to set up ODBC access to sql anywhere. This is probably the easiest way

I do not know in sql anywhere but in ASE you can get information about tables as folows:

sp_help <TABLE_NAME>

This shows the table, columns, indexes and primary key of the table. Also try

sp_helpkey <TABLE_NAME>
sp_helpconstraint <TABLE_NAME>
sp_helpjoins <TABLE_NAME_A),<TABLE_NAME_B)


List of all tables in ASE can be selected from sysobjects as follows:

select name from sysobjects where type = 'U' order by name

and list of all constraints are stored in sysconstraints table

select * from sysconstraints


Again I think a data modelling tool like PowerDesigner is your best bet.

Good luck. I hope this helps