sql 查询 一个数据库中的所有主键

SELECT

表名=case when acolorder=1 then dname else '' end,

字段序号=acolorder,

字段名=aname,

标识=case when COLUMNPROPERTY( aid,aname,'IsIdentity')=1 then '√'else '' end,

主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (

SELECT name FROM sysindexes WHERE indid in(

SELECT indid FROM sysindexkeys WHERE id = aid AND colid=acolid

))) then '√' else '' end,

类型=bname,

占用字节数=alength,

长度=COLUMNPROPERTY(aid,aname,'PRECISION'),

小数位数=isnull(COLUMNPROPERTY(aid,aname,'Scale'),0),

允许空=case when aisnullable=1 then '√'else '' end,

默认值=isnull(etext,'')

FROM syscolumns a

left join systypes b on axtype=bxusertype

inner join sysobjects d on aid=did and dxtype='U' and dname<>'dtproperties'

left join syscomments e on acdefault=eid

order by aid,acolorder

--声明变量

DECLARE @primaryKey varchar(50)

--给变量赋值

select @primaryKey=COLUMN_NAME from INFORMATION_SCHEMACONSTRAINT_COLUMN_USAGE

WHERE CONSTRAINT_NAME=(select CONSTRAINT_NAME from INFORMATION_SCHEMATABLE_CONSTRAINTS

WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_NAME='表的名字')

--输出得到的结果

select @primaryKey

select from 病人信息表where ( 病人ID not in (select 病人ID from 科室病人住院表 where 科室病人住院表病人ID = 病人信息表病人ID))

这样，科室病人住院表与病人信息表中相同的病人ID就不会显示出来，但你还有个本次住院ID，我不知道对应病人信息表是哪个字段，所以没法写上去。

select pg_constraintconname as pk_name from pg_constraint inner join pg_class

on pg_constraintconrelid = pg_classoid where pg_classrelname = 'yourtablename' and pg_constraintcontype='p'

这个可以显示yourtablename表的主键

select pg_constraintconname as pk_name,pg_attributeattname as colname,pg_typetypname as typename from

pg_constraint inner join pg_class

on pg_constraintconrelid = pg_classoid

inner join pg_attribute on pg_attributeattrelid = pg_classoid

and pg_attributeattnum = pg_constraintconkey[1]

inner join pg_type on pg_typeoid = pg_attributeatttypid

where pg_classrelname = 'yourtablename'

and pg_constraintcontype='p'

这个可以显示出主键名，和主键关联的字段名,和字段名类型

以上就是关于sql 查询 一个数据库中的所有主键全部的内容，包括:sql 查询 一个数据库中的所有主键、SQL怎么获取一个表中的主键列名，并把列明赋值给一个变量、SQL中 如果一个表的主键是2个字段的复合主键 如何用NOT IN来提取该表的主键等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！