获取数据库中所有主键的列表 [英] Get a List of all Primary Keys in a Database

问题描述

这是最好的方法 - 获取数据库中所有主键的列表 - 还是有更好的东西？

Is this the best way to - Get a List of all Primary Keys in a Database - or is there something better?

SELECT
KCU.TABLE_NAME AS Table_Name,
KCU.CONSTRAINT_NAME AS Constraint_Name,
KCU.COLUMN_NAME AS COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA
AND KCU.TABLE_NAME = TC.TABLE_NAME
WHERE
TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
KCU.TABLE_SCHEMA, KCU.TABLE_NAME, KCU.CONSTRAINT_NAME

推荐答案

查看链接

EXEC sp_pkeys '<tablename>'
EXEC sp_helpconstraint '<tablename>'

sp_pkeys将为参与
主键的每个
列返回一行。您最感兴趣的
列可能是COLUMN_NAME和PK_NAME。

sp_pkeys will return a row for each column that participates in the primary key for . The columns you are likely most interested in are COLUMN_NAME and PK_NAME.

sp_helpconstraint将列出所有
约束，包括引用该表的
外键。
在第一个记录集中，
只有一个名为Object name
的列（无用的，因为这是你传入的
）。在第二个
结果集中，将有以下
列：constraint_type，
constraint_name和constraint_keys。

sp_helpconstraint will list all constraints for , including foreign keys that reference the table. In the first recordset, there will only be a column called Object Name (kind of useless, since that's what you passed in). In the second resultset, there will be the following columns: constraint_type, constraint_name, and constraint_keys.

这篇关于获取数据库中所有主键的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！