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'

方式二：

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;

方式三：

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')

–查询所有表的主键信息

方式一：

SELECT A.NAME AS 表名,B.NAME AS 主键名

FROM  SYSOBJECTS A

    JOIN SYSOBJECTS B

        ON A.ID=B.PARENT_OBJ

        AND A.XTYPE='U' AND B.XTYPE='PK'

方式二：

SELECT a.name AS '表名',
  e.name AS '主键字段'
 
FROM sysobjects AS a          --对象表，结合a.xtype='U'条件，查用户表
 LEFT JOIN sysobjects AS b --对象表，结合b.xtype='PK'条件，查主键约束
  ON a.id=b.parent_obj 
 LEFT JOIN sysindexes AS c --索引表，根据（主键）约束名称匹配，查对应字段索引
  ON a.id=c.id AND b.name=c.name
 LEFT JOIN sysindexkeys AS d --索引中对应键、列的表，根据索引匹配，查字段id
  ON a.id=d.id AND c.indid=d.indid
 LEFT JOIN syscolumns AS e --字段表，根据字段id匹配，查字段名称
  ON a.id=e.id AND d.colid=e.colid
WHERE a.xtype='U' 
  AND b.xtype='PK'