gpt4 book ai didi

sql - 如何确定复合键中的列位置

转载 作者:行者123 更新时间:2023-12-04 04:37:57 25 4
gpt4 key购买 nike

我需要识别数据库中的所有主键。以下代码似乎可以很好地完成这项工作:

SELECT i.name AS IndexName,
OBJECT_NAME (ic.OBJECT_ID) AS TableName,
COL_NAME (ic.OBJECT_ID, ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

我的问题是某些键是复合键。此查询确定哪些键是复合键(sys.indexes 表中特定 indexName 的多行),但它没有显示顺序。我需要知道这一点,因为:
PRIMARY KEY CLUSTERED 
(
[bl_id] ASC,
[fl_id] ASC,
[rm_id] ASC
)

不等同于:
PRIMARY KEY CLUSTERED 
(
[rm_id] ASC
[fl_id] ASC,
[bl_id] ASC,

)

最佳答案

key_ordinal 似乎可以解决问题:

SELECT i.name AS IndexName,
OBJECT_NAME (ic.OBJECT_ID) AS TableName,
COL_NAME (ic.OBJECT_ID, ic.column_id) AS ColumnName,
ic.Key_ordinal as ColumnOrder
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
ORDER BY ic.OBJECT_ID, ic.Key_ordinal

关于sql - 如何确定复合键中的列位置,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19412025/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com