3

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.

Community
  • 1
  • 1
Iyas
  • 520
  • 1
  • 11
  • 40

1 Answers1

2

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.

aF.
  • 64,980
  • 43
  • 135
  • 198
  • 1
    Thanks for your answer. But, sorry for not being clear in the first place. What I mean is, the equivalent in term of column output.for MySQL SHOW KEYS FROM: Non_unique, Key_name, Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment. for MySQL DESCRIBE: Field, Type, Null, Key, Default, Extra. I am aware of the different between MySQL and Sybase. Anyway, I'll try play around with your code. – Iyas Dec 15 '11 at 00:58