With SQL Server 2005, there are new ways to obtain richer metadata in a database and more efficiently. We have introduced new catalog views that exposes all the metadata that SQL Server uses and can be created by various DDL statements. The older ANSI SQL style INFORMATION_SCHEMA views are also still available if you want to write portable queries. I already posted a tip about finding dependencies http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx between various objects using the new catalog views. Here are two queries that show you how to retrieve primary/unique key details for all tables in a database:
 
-- ANSI SQL compatible and works from SQL70 onwards:
select kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION
  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
  join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
    on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
   and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
   and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
   and kcu.TABLE_NAME = tc.TABLE_NAME
 where tc.CONSTRAINT_TYPE in ( 'PRIMARY KEY', 'UNIQUE' )
 order by kcu.TABLE_SCHEMA, kcu.TABLE_NAME, tc.CONSTRAINT_TYPE, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION;
 
-- SQL Server 2005 specific:
select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
     , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
     , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
  from sys.key_constraints as k
  join sys.tables as t
    on t.object_id = k.parent_object_id
  join sys.schemas as s
    on s.schema_id = t.schema_id
  join sys.index_columns as ic
    on ic.object_id = t.object_id
   and ic.index_id = k.unique_index_id
  join sys.columns as c
    on c.object_id = t.object_id
   and c.column_id = ic.column_id
 order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, ORDINAL_POSITION;