Identifying Sybase tables, fields, keys, constraints

foreign-keys primary-key key sybase sysobjects
Karl picture Karl · Feb 9, 2011 · Viewed 17.3k times · Source

I'm trying to set up a Sybase query that will give me the following output:

Table     KeyType      KeyNumber      Column
table1    PK           1              table1_id
table1    FK           2              table2_id    
table1    FK           3              table3_id
table1    FK           4              table4_id
table1    Unique       5              table1_abc
table1    Unique       5              table1_def

In other words, I need the PK for each table, and every foreign key it has, as well as every unique key (not where a key has more than one element, such as the unique key above, this is identified by having the same KeyNumber).

I'm guessing I need to use sysobject, syscolumns, syskeys and sysconstraints but I can't seem to figure out how they interlink.

Thanks
Karl

Answer

SimonC picture SimonC · Feb 18, 2011

This is a start:

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)

It does not include the key ID, for that I guess you could somehow hash the non-null table ID and keyN columns to produce a unique ID for the key.

It also does not include unique indexes. For that you would want to UNION with something along the lines of:

SELECT 
    t.name, 
    'Unique',
    c.name
FROM 
    sysobjects t INNER JOIN 
    syscolumns c ON c.id = t.id INNER JOIN
    sysindexes i ON i.id = t.id
WHERE t.type = 'U'

Check out the Sybase manual page for sysindexes on how to filter it.

Related questions

Is it fine to have foreign key as primary key?

I have two tables: User (username, password) Profile (profileId, gender, dateofbirth, ...) Currently I'm using this approach: each Profile record has a field named "userId" as foreign key which links to the User table. When a user registers, his Profile record …

Read More
Foreign key referring to primary keys across multiple tables?

I have to two tables namely employees_ce and employees_sn under the database employees. They both have their respective unique primary key columns. I have another table called deductions, whose foreign key column I want to reference to primary …

Read More
How to add composite primary key to table

create table d(id numeric(1), code varchar(2)) After I create the above table how can I add a composite primary key on both fields and also a foreign key?

Read More