• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

SQL Server 2012 查询数据库中表格主键信息

开发技术 开发技术 2周前 (04-08) 3次浏览

–查询特定表的主键信息

方式一:


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'

程序员灯塔
转载请注明原文链接:SQL Server 2012 查询数据库中表格主键信息
喜欢 (0)