我想使用SQL Server数据库的SQL query来获取特定表的主键。
在MySQL中,我使用以下查询来获取表主键:
SHOW KEYS FROM tablename WHERE Key_name = 'PRIMARY'
对于SQL Server,上述查询的等价物是什么?
如果有一个查询同时适用于MySQL和SQL Server,那么这将是一个理想的情况。
发布于 2010-10-15 22:01:01
我找到了另一个:
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
;
我已经在SQL Server 2003/2005上对此进行了测试
发布于 2010-10-14 14:47:29
我还为SQL Server找到了另一个:
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'
发布于 2010-10-14 13:39:20
使用SQL SERVER 2005,您可以尝试
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
可在SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database找到
https://stackoverflow.com/questions/3930338
复制相似问题