MySQL to Sybase

Question

What is the equivalent in Sybase for MySQL:

1. SHOW KEYS FROM
2. DESCRIBE

I've just referred these links, but still in confuse:

Identifying Sybase tables, fields, keys, constraints

Discover primary / unique keys in Sybase ASE

I'm guessing that it has something to do with sysobject and syscolumns tables and sp_helpconstraint, but couldn't reach the solution.

Answer 1

SHOW KEYS FROM

With this code you can get the list of keys from a table.

SELECT 
    t.name, 
    CASE k.type 
        WHEN 1 THEN 'PK' 
        WHEN 2 THEN 'FK'
        WHEN 3 THEN 'Common'
    END,
    c.name
FROM 
    sysobjects t INNER JOIN 
    syscolumns c ON c.id = t.id INNER JOIN
    syskeys k ON k.id = t.id AND c.colid IN (k.key1, k.key2, k.key3, k.key4, k.key5, k.key6, k.key7, k.key8)
WHERE 
    t.type = 'U' AND k.type in (1,2)

In order to the syskeys join to work properly, you have to use sp_primarykey and sp_foreignkey. With this commands you add a row to the syskeys table.

You can also use sp_helpindex 'tablename'. Info about constrainst (PRIMARY and FOREIGN included) can also be obtained with sp_helpconstraint 'tablename'.

---

DESCRIBE

To get the describe info just do sp_help 'tablename'.

---

---

In order to get exactly what you need you can create the selects that you need by editing the stored procedures that I referenced above and creating new ones. sp_help, sp_helpindex and sp_helpconstraint are located in syssystemprocs database.