SQLServer: 使用 SQL 查询获取表主键

我想使用 SQL Server数据库的 SQL 查询来获取特定表的主键。

在 MySQL中，我使用以下查询来获得表的主键:

SHOW KEYS FROM tablename WHERE Key_name = 'PRIMARY'

对于 SQL Server，上面的查询等价于什么。

如果有一个查询，将工作的 MySQL和 SQL Server，那么它将是一个理想的情况。

558971 次浏览
小开

根据记忆，要么是这个

SELECT * FROM sys.objects
WHERE type = 'PK'
AND  object_id = OBJECT_ID ('tableName')

或者这个。

SELECT * FROM sys.objects
WHERE type = 'PK'
AND  parent_object_id = OBJECT_ID ('tableName')

我认为其中一个可能会起作用，这取决于数据是如何存储的 但是恐怕我无法访问 SQL 来实际验证这一点。

小开

使用 SQLSERVER2005，您可以尝试

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

在 SQLSERVER-2005-在数据库中查找具有主键约束的表发现的

小开 
select *
from sysobjects
where xtype='pk' and
parent_obj in (select id from sysobjects where name='tablename')

这将在 sql2005中工作

小开

我将给您的代码不仅可以工作并检索键，还可以从 SQLServer 中的表中检索大量数据。在 SQLServer2k5/2k8中进行测试，不知道大约是2k。好好享受吧！

SELECT DISTINCT
sys.tables.object_id AS TableId,
sys.columns.column_id AS ColumnId,
sys.columns.name AS ColumnName,
sys.types.name AS TypeName,
sys.columns.precision AS NumericPrecision,
sys.columns.scale AS NumericScale,
sys.columns.is_nullable AS IsNullable,
(   SELECT
COUNT(column_name)
FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE
TABLE_NAME = sys.tables.name AND
CONSTRAINT_NAME =
(   SELECT
constraint_name
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
TABLE_NAME = sys.tables.name AND
constraint_type = 'PRIMARY KEY' AND
COLUMN_NAME = sys.columns.name
)
) AS IsPrimaryKey,
sys.columns.max_length / 2 AS CharMaxLength /*BUG*/
FROM
sys.columns, sys.types, sys.tables
WHERE
sys.tables.object_id = sys.columns.object_id AND
sys.types.system_type_id = sys.columns.system_type_id AND
sys.types.user_type_id = sys.columns.user_type_id AND
sys.tables.name = 'TABLE'
ORDER BY
IsPrimaryKey

您只能使用主键部分，但我认为其余部分可能会很方便。 最好的问候, 大卫

小开

我还为 SQLServer 找到了另一个方法:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = 'TableName' AND TABLE_SCHEMA = 'Schema'
小开

它也是(Transact-SQL) ... 根据 BOL。

-- exec sp_serveroption 'SERVER NAME', 'data access', 'true' --execute once


EXEC sp_primarykeys @table_server = N'server_name',
@table_name = N'table_name',
@table_catalog = N'db_name',
@table_schema = N'schema_name'; --frequently 'dbo'
小开
最佳答案

又找到一个:

SELECT
KU.table_name as TABLENAME
,column_name as PRIMARYKEYCOLUMN
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC


INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
AND KU.table_name='YourTableName'


ORDER BY
KU.TABLE_NAME
,KU.ORDINAL_POSITION
;

我已经在 SQLServer2003/2005上进行了测试

小开

这应该列出所有的约束，最后您可以放置您的过滤器

/* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/
WITH   ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME)
AS
(
SELECT  CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) ,
PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE=  oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME='' ,
REFERENCE_COL_NAME=''


FROM sys.key_constraints as PKnUKEY
INNER JOIN sys.tables as PKnUTable
ON PKnUTable.object_id = PKnUKEY.parent_object_id
INNER JOIN sys.index_columns as PKnUColIdx
ON PKnUColIdx.object_id = PKnUTable.object_id
AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
INNER JOIN sys.columns as PKnUKEYCol
ON PKnUKEYCol.object_id = PKnUTable.object_id
AND PKnUKEYCol.column_id = PKnUColIdx.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=PKnUTable.name
AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
UNION ALL
SELECT  CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE='FK',
PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) ,
REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30))
FROM sys.foreign_key_columns FKC
INNER JOIN sys.sysobjects oConstraint
ON FKC.constraint_object_id=oConstraint.id
INNER JOIN sys.sysobjects oParent
ON FKC.parent_object_id=oParent.id
INNER JOIN sys.all_columns oParentCol
ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
INNER JOIN sys.sysobjects oReference
ON FKC.referenced_object_id=oReference.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=oParent.name
AND oParentColDtl.COLUMN_NAME=oParentCol.name
INNER JOIN sys.all_columns oReferenceCol
ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/


)


select * from   ALL_KEYS_IN_TABLE
where
PARENT_TABLE_NAME  in ('YOUR_TABLE_NAME')
or REFERENCE_TABLE_NAME  in ('YOUR_TABLE_NAME')
ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME;

参考请阅读通过 http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx

小开 
SELECT COLUMN_NAME FROM {DATABASENAME}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME LIKE '{TABLENAME}' AND CONSTRAINT_NAME LIKE 'PK%'

哪里
{ DATABASENAME } = 服务器中的数据库 AND
{ TABLENAME } = 要从中查看主键的表名。

注意: 输入数据库名称和表名称时不带括号。

小开

请记住，如果希望获得精确的主字段，则需要将 TABLE _ NAME 和 TABLE _ SCHEMA 放入条件中。

这种解决方案应该奏效:

select COLUMN_NAME from information_schema.KEY_COLUMN_USAGE
where CONSTRAINT_NAME='PRIMARY' AND TABLE_NAME='TABLENAME'
AND TABLE_SCHEMA='DATABASENAME'

提交答案