How to get the Column Name of primary key of a table?

How to get the Column Name of primary key of a table?
Thanks.
--Using SQL 2005 devFrank Lee wrote:
> How to get the Column Name of primary key of a table?
> Thanks.
> --Using SQL 2005 dev
>
>
This example will return the ordered columns that make up the primary
key of the ContactCreditCard table in the AdventureWorks database:
use AdventureWorks
go
select b.TABLE_NAME, COLUMN_NAME, a.ORDINAL_POSITION
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b on a.constraint_name =
b.constraint_name
where b.CONSTRAINT_TYPE = 'PRIMARY KEY' and b.TABLE_NAME =
'ContactCreditCard'
order by b.TABLE_NAME, a.ORDINAL_POSITION
The results will return all of the fields that make up the primary key:
TABLE_NAME COLUMN_NAME ORDINAL_POSITION
ContactCreditCard ContactID 1
ContactCreditCard CreditCardID 2|||You might find this procedure as a useful starting point, sorry about the
formatting. One thing I know it doesn't take into account is compound
*foreign* keys. This was developed for a system that doesn't have any of
those...
Basically, this will show every *user* table in your database, whether the
column is in the primary key or not, the name of the column, the type (as
well as length or precision/scale where appropriate), whether the column
allows nulls, and the foreign key reference if it exists (again, I only
dealt with single column references).
This exclusively uses the new sys. catalog views and so you do not need to
qualify objects with built-ins like objectproperty('isMsShipped') etc.
There might be a way to eliminate some of the joins, especially if you don't
need all of the information. Just providing you with what I developed in a
pinch for my requirements.
CREATE PROCEDURE dbo.ShowColumnList
AS
BEGIN
SET NOCOUNT ON;
SELECT
Table_Name = object_name(c.Object_id),
In_Key = CASE WHEN keys.Column_Name IS NOT NULL THEN 'Yes' ELSE 'No' END,
Column_Name = c.name,
Type_Name = UPPER(t.name + CASE
WHEN t.name IN ('VARCHAR','NVARCHAR') AND c.Max_Length = -1 THEN '(MAX)'
WHEN t.name IN ('NCHAR','NVARCHAR') THEN '('+RTRIM(c.Max_Length/2)+')'
WHEN t.name IN ('CHAR', 'VARCHAR') THEN '('+RTRIM(c.Max_Length)+')'
WHEN t.name IN ('NUMERIC','DECIMAL') THEN
'('+RTRIM(c.precision)+','+RTRIM(c.scale)+')'
ELSE '' END
+ CASE c.is_identity WHEN 1 THEN ' -- IDENTITY' ELSE '' END),
Allows_Nulls = CASE c.is_nullable WHEN 1 THEN 'Yes' ELSE 'No' END,
Foreign_Key = COALESCE(fkeys.Ref_Table_Name+'.'+fkeys.Ref_Column_Name, '')
FROM
sys.columns c
INNER JOIN
sys.types t
ON
c.system_type_id = t.system_type_id
AND t.name != 'SYSNAME'
INNER JOIN
sys.tables tb
ON
c.object_id = tb.object_id
LEFT OUTER JOIN
(
SELECT
Table_Name = OBJECT_NAME(t.object_id),
Column_Name = c.name
FROM
sys.index_columns ic WITH (NOLOCK)
INNER JOIN
sys.indexes i WITH (NOLOCK)
ON ic.index_id = i.index_id
AND i.object_id = ic.object_id
INNER JOIN
sys.tables t WITH (NOLOCK)
ON i.object_id = t.object_id
INNER JOIN
sys.key_constraints k WITH (NOLOCK)
ON k.name = i.name
AND k.type='PK'
INNER JOIN
sys.columns c WITH (NOLOCK)
ON c.Object_id = t.object_id
AND c.column_id = ic.column_id
) keys
ON
keys.Column_Name = c.Name
AND keys.Table_Name = tb.Name
LEFT OUTER JOIN
(
SELECT
Table_Name = OBJECT_NAME(k.Parent_Object_ID),
Column_Name = c1.name,
Ref_Table_Name = OBJECT_NAME(k.Referenced_Object_ID),
Ref_Column_Name = c2.name
FROM
sys.foreign_keys k WITH (NOLOCK)
INNER JOIN
sys.foreign_key_columns kc WITH (NOLOCK)
ON
k.object_id = kc.constraint_object_id
INNER JOIN
sys.columns c1 WITH (NOLOCK)
ON
c1.object_id = kc.parent_object_id
AND kc.parent_column_id = c1.column_id
INNER JOIN
sys.columns c2 WITH (NOLOCK)
ON
c2.object_id = kc.referenced_object_id
AND kc.referenced_column_id = c2.column_id
) fkeys
ON
fkeys.Column_Name = c.Name
AND fkeys.Table_Name = tb.Name
ORDER BY
OBJECT_NAME(c.object_id),
c.column_id;
END
GO
"Frank Lee" <Reply@.to.newsgroup> wrote in message
news:%23kwAsBQDGHA.916@.TK2MSFTNGP10.phx.gbl...
> How to get the Column Name of primary key of a table?
> Thanks.
> --Using SQL 2005 dev
>
>