Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    4

    Unanswered: How to find the primary key in a sybase table??

    For example,I have a product table which have a clustered index on columns eanno and TPNB.Clustered index are primary keys by default.
    So can i assume that both the columns are primary keys?Is there any other way find using System tables.(Sybase version 11.5)


    Thanks in advance.

  2. #2
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hello,

    I'm using this query in ASE 15.5 and 12.5.4. I assume it will work on your version as well.
    Position yourself on the db you want and run the query. This gives you all the tables, if you only want one, add the clause (o.name like 'tablename') and remove the order by.

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


    This should give you what you desire, the identity columns will also appear.

    Hope it helps.
    Last edited by Catarrunas; 06-15-12 at 10:52.

  3. #3
    Join Date
    May 2012
    Posts
    4
    Thanks for your quick reply ..

    But I got the tablename and its corresponding clustered index when I executed the above query.Not sure the problem is because of version .

    Any other way to get primary key in sybase ASE 11.5.1??

  4. #4
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Okay,
    Let me see if i got you right, you want the fields of the primary key? If so.

    Code:
    select o.name, i.indid, i.keycnt
    from sysobjects o, sysindexes i
    where o.id=i.id
    and i.indid = 2
    and o.type = 'U'
    and o.name = 'YOUR TABLENAME'
    order by o.name
    Output will be:
    Name indid keycnt
    A 2 3

    Name = Table Name
    indid = index id
    keycnt = number of fields in pk

    After you do, considering the number of keycnt, the following:

    Code:
    select index_col ('A',2,1) + ',' + index_col ('A',2,2) + ',' + index_col ('A',2,3)

    This will give you the fieds, but is a manual process, it would be nice if do an sp, that receives the Tablename and returns the line with the info. To automatize the operation.

    Hope it helps.
    Last edited by Catarrunas; 06-15-12 at 10:53.

  5. #5
    Join Date
    May 2012
    Posts
    4
    Thanks Again :-)

    As mentioned earlier ,I got the productno,and barcode as a primary key (which are part of clustered index keys in my table).
    I assume Primary keys are clustered index by default.So my doubt is,Is there a table can have 2 primary keys??

  6. #6
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Oh ok
    I see now is much simpler.

    A table cannot have more than one primary key or one clustered index.
    What you can have is as many nonclustered indexes as you want with the fields that you want.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •