>>>>I'm trying to write a query of the system tables in my DB where I pass it the id of the table - and what I want to get back is the colid and name of the column that is the primary key for the table.
>>>
>>>It's not recomended directly query SQl Server system tables. If you're using SQL 2000 see if you can get that info from Information Schema views.
>>
>>Thanks Sergey - I just did a search in BOL and it returned over a hundered items when seraching for 'Information Schema views'. Can you perhaps narrow the scope of where I should look to find specifically what I am looking for? I appreciate your assistance.
>
>In the BOL index type 'INFORMATION SCHEMA' and you'll get the list of views. Anyway, try this from QA.

USE pubs
>SELECT OBJECT_ID( isv.constraint_name)
>	FROM (
>SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
>	WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
>		AND table_name = 'titles') isv

BTW, they are located in the >master DB and you can view there code source if you've to dig into system tables.

The object_Id that this query returns - does not appear to correspond to a field in INFORMATION_SCHEMA.COLUMNS view

I did the following query and there was no Object_ID that corresponded to the output from the query above.

select * from INFORMATION_SCHEMA.COLUMNS where table_name = 'titles'

Returns no Object_ID - so that probably leaves querying the system tables directly to return the field name (or field names) that represent the primary key.

This works (if only one field is the PK) - but it is ugly and I'm sure there is an easier way to do this ..

Note: I hardcoded the id of the 'titles' table in the PUBS db.

select syscolumns.name AS PKFieldName
  from syscolumns 
  where syscolumns.id = 2121058592
  and syscolumns.colid = 
	(select sysindexkeys.colid 
	from sysindexkeys 
	where sysindexkeys.indid = 1 
	and sysindexkeys.id = 
		(select sysindexes.id from sysindexes 
		where sysindexes.id = 2121058592
 		and sysindexes.indid = 1))