SQL Server 查询表的主键的两种方式

方式1：

select b.column_name
from information_schema.table_constraints a
inner join information_schema.constraint_column_usage b
on a.constraint_name = b.constraint_name
where a.constraint_type = 'PRIMARY KEY' and a.table_name = 'products'
go

 

方式2：

SELECT a.name 
  FROM   syscolumns a 
  inner  join sysobjects d on a.id=d.id       
  where  d.name='products' and exists(SELECT 1 FROM sysobjects where xtype='PK' and  parent_obj=a.id and name in (  
  SELECT name  FROM sysindexes   WHERE indid in(  
  SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid  
)))

 

方式3：

SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE   
WHERE TABLE_NAME='PDA_xjData' 

 

方案4（结合方案3，实用）：

declare @tableName varchar(50)
set @tableName = 'b_attenceHz'

select a.colname,a.typename,a.length,a.typename,a.table_name,a.prec,a.scale,a.offset,(case when isnull(COLUMN_NAME,'')<>'' then 1 else 0 end) as isPrimaryKey
  from (
SELECT syscolumns.name as colname ,systypes.name as typename,syscolumns.isnullable,syscolumns.length,@tableName as table_name
,syscolumns.prec,syscolumns.scale,syscolumns.offset
FROM syscolumns, systypes
WHERE syscolumns.xusertype = systypes.xusertype
AND syscolumns.id = object_id(@tableName)) a 
left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE b on a.colname = b.column_name and a.table_name=b.TABLE_NAME
order by a.offset desc