0

I'm using Sybase ASE 15.5 and a stranger to this database. Straight to the point--> I'm looking for a sql query that would help me get the primary keys for all tables in sybase along with the column names on which the primary key is declared. For example, if I have the following tables, organization having primary key PK_org_id on the column org_id org_alias having primary key PK_alias_id on the column alias_id org_temp having primary key PK_org_temp_id on the columns (org_id,org_name)

then the query should return me with:

  • Table_Name PK_Name Column_name
  • Organization PK_org_id org_id
  • Org_alias PK_alias_id alias_id
  • Org_temp PK_org_temp_id org_id,org_name

I've tried the below query:

select  o.name , i.name
from   sysobjects  o,   sysindexes   i   
where o.id=i.id  
and i.indid = 1  
and o.type = 'U'

but it only returns me the table name with its primary key. I want to have the column name too.

Please help!

NewUser3542
  • 29
  • 1
  • 4

2 Answers2

1

Use the built_in function index_col(object_name, indexid, N [,owner_id]). This lets you retrieve the Nth column of a particular index. Call it multiple times with different values for N, for example by joining it with master..spt_values where type = 'P' and supplying the number column as N.

RobV
  • 2,263
  • 1
  • 11
  • 7
  • Thanks for your reply @Rob. I'm looking specifically for a sql query that would output me the table name, primary key name and the columns on which it is defined. Can you also share that? – NewUser3542 Jan 16 '15 at 10:58
1

If you don't want to code your own query, then look at the catalog procedures that are included with ASE. 'sp_pkeys' should give you what you want.

Pang
  • 9,564
  • 146
  • 81
  • 122
RobV
  • 2,263
  • 1
  • 11
  • 7