SQL 使用 TSQL 确定表的主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1525672/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Determine a table's primary key using TSQL
提问by rein
I'd like to determine the primary key of a table using TSQL (stored procedure or system table is fine). Is there such a mechanism in SQL Server (2005 or 2008)?
我想使用 TSQL 确定表的主键（存储过程或系统表都可以）。SQL Server（2005 或 2008）中是否有这样的机制？
回答by Stuart Ainsworth
This should get you started:
这应该让你开始：
SELECT
*
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON tc.CONSTRAINT_NAME = ccu.Constraint_name
WHERE
tc.TABLE_NAME = 'TableName' AND
tc.CONSTRAINT_TYPE = 'Primary Key'
回答by Jason Punyon
How about
怎么样
sp_pkeys 'TableName'
回答by Philip Kelley
Here's one based on system tables from SQL 2005 (99% sure it'd work in 2008). This will list all PKs for all user-defined tables, with all columns and some extra fluff that could be removed. Add parameters to pick out a table at a time.
这是一个基于 SQL 2005 中的系统表的表（99% 确定它可以在 2008 年工作）。这将列出所有用户定义表的所有 PK，所有列和一些可以删除的额外内容。添加参数以一次选择一个表。
SELECT
schema_name(ta.schema_id) SchemaName
,ta.name TableName
,ind.name
,indcol.key_ordinal Ord
,col.name ColumnName
,ind.type_desc
,ind.fill_factor
from sys.tables ta
inner join sys.indexes ind
on ind.object_id = ta.object_id
inner join sys.index_columns indcol
on indcol.object_id = ta.object_id
and indcol.index_id = ind.index_id
inner join sys.columns col
on col.object_id = ta.object_id
and col.column_id = indcol.column_id
where ind.is_primary_key = 1
order by
ta.name
,indcol.key_ordinal
回答by LukeH
SELECT ccu.COLUMN_NAME, ccu.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
WHERE tc.TABLE_CATALOG = 'Your_Catalog' -- replace with your catalog
AND tc.TABLE_SCHEMA = 'dbo' -- replace with your schema
AND tc.TABLE_NAME = 'Your_Table' -- replace with your table name
AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
回答by Jacob G
exec [sys].[sp_primary_keys_rowset] @table_name= 'TableName'
回答by chugh97
EXEC sp_Pkeys @tableName
回答by JT Turner
The simplest way is this!
最简单的方法是这样！
select object_id from sys.objects
where parent_object_id = OBJECT_ID(N'FACounty')
and [type] = N'PK'
回答by sqlconsumer.net
You're better off using INFORMATION_SCHEMA.KEY_COLUMN_USAGE, as you can access the key ordering information (ORDINAL_POSITION) which is very important to know.
最好使用INFORMATION_SCHEMA.KEY_COLUMN_USAGE，因为您可以访问关键订购信息 ( ORDINAL_POSITION)，了解这一点非常重要。
SELECT
kcu.*
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON tc.TABLE_NAME = kcu.TABLE_NAME AND
tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
ORDER BY
tc.TABLE_NAME,
tc.CONSTRAINT_NAME,
kcu.ORDINAL_POSITION
回答by Pasi Savolainen
If you already know the name of the key you're interested in, following works:
如果您已经知道您感兴趣的密钥的名称，请执行以下操作：
-- Assuming you have schema "Example" and the primary key name is "PK_Item"
-- Notice that name of table is irrelevant here but is "Foobar" here
IF (OBJECT_ID('Example.PK_ITEM') IS NULL)
BEGIN
ALTER TABLE [Example].Foobar ADD CONSTRAINT
PK_Item PRIMARY KEY ...
END
