How to get primary keys for all tables in Sybase

Question

I have got a requirement of creating data dictionary from all the user tables in a sybase ase database. I can't install tools like Sybase PowerDesigner, and so I have to make it possible using SQL queries.

I have composed this query by taking the help of forums, to show the table name, column name, datatype, size and not null constraint. But I am unable to find whether the column is a Primary key or not or is a part of clustered Primary key.

    SELECT O.name as "Table",
            C.name as "Column", 
            C.length as "Length",
            T.name as "Datatype",
            C.status as "Allow Null",
            CASE C.status
                WHEN 8 THEN 'NULL' 
                WHEN 0 THEN 'NOT NULL' 
            END as "NULLS"
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

Can anyone here help me to do the necessary join with the required tables to get the required flag indicating its Primary Key status. I already went through the syskeys and sysindexes table documentation but am unable to find which status there would be beneficial for my purpose.

This should help http://stackoverflow.com/questions/4943420/identifying-sybase-tables-fields-keys-constraints — www, Jan 30 '15 at 15:39

score 0 · Answer 1 · edited May 16 '19 at 11:53

Please try below Code :

    SELECT O.name as "Table",
            C.name as "Column", 
            C.length as "Length",
            T.name as "Datatype",
            C.status as "Allow Null",
            CASE C.status
                WHEN 8 THEN 'NULL' 
                WHEN 0 THEN 'NOT NULL' 
            END as "NULLS",
            ISNULL((select "Y" as PK_FLAG from syskeys K where O.id=K.id
            and K.type = 1 and (C.colid =K.key1 or C.colid =K.key2 OR C.colid =K.key3  OR C.colid =K.key4 OR C.colid =K.key5 OR C.colid =K.key6 OR C.colid =K.key7 Or C.colid =K.key8 )),"N") as Primary_Key_Flag
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

How to get primary keys for all tables in Sybase

1 Answers1