识别Sybase表，字段，键，约束 [英] Identifying Sybase tables, fields, keys, constraints

问题描述

  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唯一5 table1_abc 
 table1唯一5 table1_def $ b $换句话说，我需要每个表的PK，每个外键，每个唯一的唯一键键（不是一个键有多个元素的地方，比如上面的唯一键，这是通过具有相同的KeyNumber来标识的）。
 
 
 
我猜我需要使用sysobject，syscolumns，syskeys和sysconstraints，但我似乎无法弄清楚他们是如何相互链接的。
 
 
谢谢
 
 Karl 
解决方案
这是一个开始：
 
 
 $ $ p $ 选择
 t.name，
 CASE k.type 
当时1'PK'
当2当时'FK'
当3当'普通'
 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）
  pre> 
 
 
它不包含密钥ID，因此我想你可以以某种方式对非空的表ID和keyN列进行哈希以产生密钥的唯一ID。 
 
 
它也不包含唯一索引。为此，你需要 UNION ，其中包含以下内容： 
 
 
  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'
  
查看 sysindexes的Sybase手册页，了解如何过滤它。
 
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
解决方案
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.

                        
这篇关于识别Sybase表，字段，键，约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！