查询数据库各表与其主键

SQLserver

查询数据库各表与其主键

select a.name,max(a.pk1) 'pk1',max(a.pk2) 'pk2',max(a.pk3) 'pk3',max(a.pk4) 'pk4'
from (
Select 
    t.name,
    case when k.keyno=1 then col_name(t.object_id,k.colid) else NULL end as 'pk1',
    case when k.keyno=2 then col_name(t.object_id,k.colid) else NULL end as 'pk2',
    case when k.keyno=3 then col_name(t.object_id,k.colid) else NULL end as 'pk3',
    case when k.keyno=4 then col_name(t.object_id,k.colid) else NULL end as 'pk4'
From  sysobjects         as o
Inner Join sysindexes    as i On i.name=o.name 
Inner Join sysindexkeys  as k On k.indid=i.indid
join sys.tables as t on k.id=t.object_id and o.parent_obj=t.object_id
join sys.schemas as s on t.schema_id = s.schema_id
Where 1=1 
and o.xtype = 'PK' 
and s.name='dbo' 
) a
where 1=1
group by a.name