how do i identify which fields are primary keys???

[stiej]

hi,

how can i tell which field/s in a given table is/are the Primary Key/s????

thanks

 

[stiej]

.....that is, within a sql script.

 

[vongrunt]

Many ways... one is:

sp_pkeys <tablename>

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[hmckillop]

Another way to get all table's primary keys is

	set nocount on
	declare @vc_SQL varchar(1000)
	declare @sy_Table sysname

	create table #TableInfo
	(
		[Qual] sysname null
		,[Owner] sysname null
		,[TableName] sysname null
		,[PK_ColName] sysname null
		,[KeySeqno] smallint null
		,[PK_Name] sysname null
	)
	set @sy_Table =''
	while(@sy_Table is not null)
	begin
		set @sy_Table=(select min([name]) from [sysobjects]
				where type='U' and [name]>@sy_Table)
		if (@sy_Table is not null)
		begin
			insert into #TableInfo([Qual],[Owner],[TableName],[PK_ColName],[KeySeqno],[PK_Name])
				exec sp_pkeys @sy_Table
		end
	end

	select left([TableName],50) as 'Table'
		,left([PK_Name],50) as 'KeyName'
		,left([PK_ColName],50) as 'KeyCol'
	from #TableInfo
	order by 1
	
	drop table #TableInfo

"I'm living so far beyond my income that we may almost be said to be living apart

 

[stiej]

thanks a lot guys. very helpful. cheers!