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

Thanks.

--Using SQL 2005 dev

Frank Lee wrote:
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



Show quoteHide quote

Use of NULL and DEFAULT
Record locking within a stored procedure
How to decide whether trigger is a good idea?
compare '002' and '00²'
Evaluation of conditions