sql server programming: Dear all,
I'm using SQL Server 2005 and would like to generate a script that
would display for me the column names that are primary keys;
For this target i checked the system views and found the view
sys.key_constraints which display all primary key constraints including
the constraint name, object_id and parent_object_id:

SELECT *
FROM sys.key_constraints;
GO

However, by obtaining the object_id I thought I could get the
corresponding column name but couldn't find a view yet, so i hope some
could tell me a way how to list all column names which are primary
keys, whereby i need the column names and not the constraint names.

regards

[quoted text, click to view]

See below. A key may include multiple columns so you need at least one
join to get the column name(s). You can also use the information schema
instead of the catalog views - see my second example below.

A unique index on non-nullable columns is a key constraint in all but
name but notice that indexes aren't included in the information schema
or in the sys.key_constraints view.

SELECT
S.name AS table_schema,
O.name AS table_name,
K.name AS constraint_name,
K.type_desc AS constraint_type,
C.name AS column_name
FROM sys.objects AS O
JOIN sys.schemas AS S
ON O.schema_id = S.schema_id
JOIN sys.key_constraints AS K
ON O.object_id = K.parent_object_id
JOIN sys.indexes AS I
ON K.parent_object_id = I.object_id
AND K.unique_index_id = I.index_id
JOIN sys.index_columns AS J
ON I.object_id = J.object_id
AND I.index_id = J.index_id
JOIN sys.columns AS C
ON J.column_id = C.column_id
AND J.object_id = C.object_id
ORDER BY constraint_name, constraint_type, column_name;

SELECT C.table_schema, C.table_name,
C.constraint_name, C.constraint_type, K.column_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
ON C.constraint_schema = K.constraint_schema
AND C.constraint_name = K.constraint_name
AND C.constraint_type IN ('PRIMARY KEY','UNIQUE')
ORDER BY constraint_name, constraint_type, column_name;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--