2

In Sybase ASE, I would like to discover all primary and unique keys. I want to do something similar to what is explained in this answer:

Identifying Sybase tables, fields, keys, constraints

But unfortunately, this doesn't work for me. Somehow the syskeys table does return any rows for my own keys, only for system table keys.

What may I be doing wrong? Some missing grants? I have installed Sybase ASE 15.5 and I'm connecting with user dbo, login sa

Community
  • 1
  • 1
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509

1 Answers1

4

When you explicitly declare a key field - say in a CREATE TABLE statement - this doesn't populate the syskeys table. You would use (e.g.) sp_primarykeys to do that. This allows you to 'register' in the syskeys tables the primary, foreign keys, etc. that you would like to be discoverable by an application. You can declare a primary key in this way, even if there is no actual constraint enforced on the field in question.

The stored procedure sp_helpconstraint can be used to find all the keys (etc.) that are defined for a table.

To query for all tables in a database you'd need to use the sysindexes table and look at, e.g. the status field.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
martin clayton
  • 76,436
  • 32
  • 213
  • 198
  • @Lukas - for this weekend only, I don't have access to a Sybase server to test a query on sysindexes for you - apologies! – martin clayton Sep 04 '11 at 15:37
  • 1
    @Lukas - [this](http://www.dbforums.com/sybase/1625012-sysindexes-question-testing-unique-clustered-indexes.html) might do. – martin clayton Sep 04 '11 at 15:41
  • Hmm, I'd like to avoid using workarounds and tricks involving parsing of `SQL` as returned by `sp_helpconstraint`. Is explicitly adding keys to `syskeys` using `sp_primarykey` a common practice? I'm asking this, because I want to add support for Sybase ASE to [my database abstraction library jOOQ](http://www.jooq.org), and I'm not sure what I can expect from client code... – Lukas Eder Sep 04 '11 at 15:44
  • @Lukas - you can't rely on the syskeys table. But if you query the sysindexes table that will tell you what's actually in place. – martin clayton Sep 04 '11 at 15:45
  • Awesome, that one link did it! Thanks a lot for your help! – Lukas Eder Sep 04 '11 at 15:45