a query to find primary key

I have a query that can tell me the columns in a table. In this case "Contact". What I would like to add to my query is if the column is part of the primary key. Can that be done? how so?

Code Snippet

SELECT

c.name AS column_name,

c.column_id,

SCHEMA_NAME(t.schema_id) AS type_schema,

t.name AS type_name,

t.is_user_defined,

t.is_assembly_type,

c.max_length,

c.precision,

c.scale

FROM

sys.columns AS c

JOIN sys.types AS t ON

c.user_type_id=t.user_type_id

WHERE c.object_id = OBJECT_ID('Contact') ORDER BY c.column_id;

Thnx

Matt

You should be able to extend this query by including the table name. Look at this post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1973994&SiteID=1
|||I don't understand how to join the INFORMATION_SCHEMA tables to my existing query....|||

What about:

Code Snippet

SELECT

TABLE_NAME,

c.name AS column_name,

c.column_id,

SCHEMA_NAME(t.schema_id) AS type_schema,

t.name AS type_name,

t.is_user_defined,

t.is_assembly_type,

c.max_length,

c.precision,

c.scale

FROM

sys.columns AS c

JOIN sys.types AS t ON

c.user_type_id=t.user_type_id

JOIN INFORMATION_SCHEMA.[KEY_COLUMN_USAGE] CCU

ON OBJECT_ID(CCU.TABLE_SCHEMA + '.' + CCU.TABLE_NAME) = object_id

WHERE c.object_id = OBJECT_ID('Contact') ORDER BY c.column_id;

Jens K. Suessmeyer

http://www.sqlserver2005.de