顯示資料庫中表的主鍵

顯示資料庫中表的主鍵[@more@]

以下以sqlserver為例寫的顯示資料庫中表的主鍵

declare @objname nvarchar(50),@i int, @thiskey nvarchar(131),@keys nvarchar(2126),@indid int,@objid int
CREATE TABLE #pktab
(
table_name nvarchar(50),
pk_name nvarchar(300)
)

declare ms_crs_ind cursor local static for
select a.name
from sysobjects a where xtype='U' and name like 'T%' order by name
open ms_crs_ind
fetch ms_crs_ind into @objname
while @@fetch_status >= 0
begin
set @objid=object_id(@objname)
set @indid=(select i.index_id
from sys.indexes i join sys.stats s
on i.object_id = s.object_id and i.index_id = s.stats_id
where i.object_id and i.is_primary_key<>0)

select @keys = index_col(@objname, @indid, 1), @i = 2
if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
select @keys = @keys + '(-)'

select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'

while (@thiskey is not null )
begin
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
end
insert #pktab select @objname as tablename,@keys as pk_name
fetch ms_crs_ind into @objname
end
select * from #pktab
drop table #pktab