找SQL表的主键的方法

1:利用SQL自带的存储过程

EXEC sp_pkeys @table_name='表名'

2:利用系统表INFORMATION_SCHEMA.KEY_COLUMN_USAGE

SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE TABLE_NAME='表名'

3: 以下这个查询表结构的语句可能大家都见过：

利用syscolumns,sysindexes,sysindexkeys三个表关联可以查出主键

SELECT

(case when a.colorder=1 then d.name else '' end) N'表名',

a.colorder N'字段序号',

a.name N'字段名',

(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',

(case when (SELECT count(*)

FROM sysobjects

WHERE (name in

(SELECT name

FROM sysindexes

WHERE (id = a.id) AND (indid in

(SELECT indid

FROM sysindexkeys

WHERE (id = a.id) AND (colid in

(SELECT colid

FROM syscolumns

WHERE (id = a.id) AND (name = a.name))))))) AND

(xtype = 'PK'))>0 then '√' else '' end) N'主键',

b.name N'类型',

a.length N'占用字节数',

COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',

isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',

(case when a.isnullable=1 then '√'else '' end) N'允许空',

isnull(e.text,'') N'默认值',

isnull(g.[value],'') AS N'字段说明'

--into ##tx

FROM syscolumns a left join systypes b

on a.xtype=b.xusertype

inner join sysobjects d

on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'

left join syscomments e

on a.cdefault=e.id

left join sysproperties g

on a.id=g.id AND a.colid = g.smallid

order by object_name(a.id),a.colorder

4:

select o.name as 表名,c.name as 字段名,k.colid as 字段序号,k.keyno as 索引顺序 from sysindexes i

join sysindexkeys k on i.id = k.id and i.indid = k.indid

join sysobjects o on i.id = o.id

join syscolumns c on i.id=c.id and k.colid = c.colid

where o.xtype = 'U'

and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name)

order by o.name,k.colid

 

本文地址：http://www.45fan.com/a/question/73666.html