当前位置: 首页 > 知识库问答 >
问题:

Sybase ASE:通过查询获取真实PK和FK列表

赫连照
2023-03-14

我有一些带有主键和外键的Sybase ASE表,我想得到这些表的真实PK和FK列表。这些信息应该通过SELECT查询返回到系统表。所有使用“syskey”查询的查询都不正确,因为syskey只包含通过sp_foreignkey创建的表的逻辑引用。例如,我有以下表格:

create table tbl_pk1
(col1 int primary key,
col2 int);

create table tbl_pk3
(col1 int null,
col2 int);

sp_primarykey  'tbl_pk3', 'col1'

下面的查询将只返回tbl_pk3。

select t.name  from syskeys i INNER JOIN sysobjects t ON i.id = t.id where  t.name in ('tbl_pk1', 'tbl_pk3')

我知道什么sp_helpconstraint程序可以帮助我,但我不能在SELECT中调用它。也许有人可以帮助我查询系统表,以获得关于表的主键和外键的实际信息?

共有2个答案

丁理
2023-03-14

我使用Sybase ASA(不是ASE),但您可以尝试这个查询(它在ASA上工作)。

对于外键:

select f.* from sys.systable t 
join sys.SYSFOREIGNKEY f on t.table_id=f.primary_table_id
where t.table_name='xxx'

对于主键:

select c.* from sys.systable t
join sys.SYSCONSTRAINT c on t.object_id=c.table_object_id
where t.table_name='xxx' and c.constraint_type='P'
罗昊明
2023-03-14

以下是如何做到这一点

select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 1) column_name, 1 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 1) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 2) column_name, 2 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 2) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 3) column_name, 3 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 3) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 4) column_name, 4 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 4) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 5) column_name, 5 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 5) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 6) column_name, 6 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 6) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 7) column_name, 7 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 7) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 8) column_name, 8 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 8) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 9) column_name, 9 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 9) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 10) column_name, 10 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 10) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 11) column_name, 11 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 11) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 12) column_name, 12 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 12) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 13) column_name, 13 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 13) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 14) column_name, 14 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 14) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 15) column_name, 15 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 15) is not null         union         
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 16) column_name, 16 column_id, i.name as con_name 
    from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo'  and  status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 16) is not null         
 类似资料:
  • 我正在开发一个java应用程序,使用过程将信息保存在数据库中。我会举个例子来证明我的怀疑,因为我有点迷路了! 让我们假装我有两个不同的班级 我想把信息保存在数据库中。由于Dur1有3秒,代码PK=1,所以我将有3个插入秒,其中FK=1=Dur的PK 我的问题是,假设(在java中)我知道seg和Dur之间的所有匹配(我有连接它们的列表),我如何使用一个过程,自动地在三个seg插入中放置一个FK /

  • 本文向大家介绍sharepoint 通过CAML查询获取列表项,包括了sharepoint 通过CAML查询获取列表项的使用技巧和注意事项,需要的朋友参考一下 示例 基本范例 使用对象的set_viewXml方法SP.CamlQuery指定CAML查询以检索项目。 分页CAML查询的结果 您可以利用RowLimitCAML查询中的元素在每个查询中仅检索结果的子集。 使用get_listItemCo

  • 我有实体类用户通过@IdClass注释使用复合键 复合键: 我将Spring数据与hibernate JPA一起使用。因此,我有回购接口: 我想通过具体姓名和姓氏列表从数据库中获取所有活跃用户。例如。我想使用这样的方法 如何通过Spring数据或JPQL查询此需求?

  • 问题内容: 我想将所有mysql表的col名称放入php数组中吗? 是否对此有疑问? 问题答案: 最好的方法是使用INFORMATION_SCHEMA元数据虚拟数据库。特别是INFORMATION_SCHEMA.COLUMNS表… 它非常强大,可以为您提供大量信息,而无需解析文本(例如列类型,列是否可为空,最大列大小,字符集等)… 哦,这是标准的SQL(这是MySQL的特定扩展名)… 有关表之间的

  • 问题内容: 编写Django查询时,可以同时使用id / pk作为查询参数。 根据django的文档,我知道pk代表主键,它只是一个快捷方式。但是,尚不清楚何时应使用id或pk。 问题答案: 没关系 从实际的主键字段更加独立,即你不需要关心的主键字段是否被称为或或什么的。 如果你的模型具有不同的主键字段,则还可以提供更高的一致性。

  • 问题内容: 我有一个模特 我试图这样做来计算此查询集中的总和: 此查询有什么问题?还是有其他方法可以计算列总和? 我知道这可以通过在queryset上使用for循环来完成,但是我需要一个优雅的解决方案。 问题答案: 你可能正在寻找