查询以获取MsSQL表的唯一索引和主键 [英] Query to fetch MsSQL table unique index and primary keys

问题描述

SELECT sysobjects.xtype, syscolumns.name, sysindexkeys.indid, sysobjects.type
FROM
    syscolumns
LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
LEFT JOIN sysindexkeys ON (
     syscolumns.id = sysindexkeys.id AND syscolumns.colid = sysindexkeys.colid
)
WHERE sysobjects.name = '{$table}'
AND sysindexkeys.indid IS NOT NULL
ORDER BY sysindexkeys.indid, sysindexkeys.keyno

我实际上是在使用以下查询，以便检索列名和键号.

I'm actually using the following query in order to retreive the column name and the keyno.

问题是我的表有3个字段:

The problem is my table has 3 fields:

user_id
config_name
config_value

在user_id和config_name上具有主键.

With a primary key on user_id AND config_name.

我希望得到收藏品:

[
  ['name' => 'user_id', 'keyno' => 1],
  ['name' => 'config_name', 'keyno' => 1]
]

但是我得到了:

[
  ['name' => 'user_id', 'keyno' => 1],
  ['name' => 'config_name', 'keyno' => 2]
]

我在做什么错了?

修改: 我使用两个索引表得到相同的怪异结果

edit: I get the same weirdos results using a two index table

表: project_image_id project_id project_image_src

table: project_image_id project_id project_image_src

project_image_id上​​的PK以及project_id和project_image_src上的唯一索引

PK on project_image_id AND unique index on project_id AND project_image_src

预期:

[
  ['name' => 'project_image_id', 'keyno' => 1],
  ['name' => 'project_id', 'keyno' => 2]
  ['name' => 'project_image_src', 'keyno' => 2]
]

但是我得到了:

[
  ['name' => 'project_image_id', 'keyno' => 1],
  ['name' => 'project_id', 'keyno' => 1]
  ['name' => 'project_image_src', 'keyno' => 2]
]

推荐答案

对我来说似乎正确，您的索引有2列，user_id的位置为1，config_name的位置为2

That seems correct to me, your index has 2 columns, user_id has position 1 and config_name has position 2

从BOL键号开始:列在索引中的位置

From BOL keyno: Position of the column in the index

您为什么认为它们都为1?

why do you think they would both be 1?

在这里，您需要重新连接到sysobject表，但是在索引上而不是表本身

Here you go, you needed to join back to the sysobject table but on the index not the table itself

SELECT s2.xtype, syscolumns.name, sysindexkeys.indid, sysobjects.type
FROM
    syscolumns
LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
LEFT JOIN sysindexkeys ON (
     syscolumns.id = sysindexkeys.id AND syscolumns.colid = sysindexkeys.colid
)
join sysobjects s2 on s2.parent_obj = sysindexkeys.id
WHERE sysobjects.name = '{$table}'
and s2.type = 'K'
AND sysindexkeys.indid IS NOT NULL
ORDER BY sysindexkeys.indid, sysindexkeys.keyno

这篇关于查询以获取MsSQL表的唯一索引和主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！