Name of Primary Key columns on a table

• our spt

  SSC Veteran

  Points: 234

  More actions

  May 16, 2005 at 7:02 am

  #66115

  Hi,

  I would like to know the way to identify the name of the primary key column(s) on a table (whether single column primary key or multi column primary key) programmatically in SQL Server (looking at something like sysobjects, syscolumns or whatever).

  Thanks in advance

  ourspt

   

• kingram-15027

  Ten Centuries

  Points: 1074

  More actions

  May 16, 2005 at 7:12 am

  #559004

  i found this on this sites SCRIPTS area.. if this is not what you want you should check it out...

  Find Constraints on a table

  By: rubakuma

  This script would help find primary key, foriegn key, unique constraints,

   check constraints on a table. With slight modification you can use it to find various information about a table.  This Content Sponsored by:  

  select b.COLUMN_NAME

  from INFORMATION_SCHEMA.table_constraints a

  inner join INFORMATION_SCHEMA.key_column_usage b on

   a.CONSTRAINT_CATALOG = b.CONSTRAINT_CATALOG and

   a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and

   a.CONSTRAINT_NAME = b.CONSTRAINT_NAME

  where a.TABLE_CATALOG = '<db Name>'

  and a.TABLE_SCHEMA = '<owner Name'

  and a.TABLE_NAME = '<table Name>'

  and a.CONSTRAINT_TYPE = '<constraint Type>'

  order by b.ordinal_position

   

• David.Poole

  SSC Guru

  Points: 76035

  More actions

  May 16, 2005 at 7:23 am

  #559008

  sp_help 'your table name here'

  Brings back all sorts of information on the table including the primary key.

  LinkedIn Profile

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  May 16, 2005 at 7:42 am

  #559015

  Another way to list a database's all tables with the names of PK constraints:

  select  table_name,

          constraint_name

  from    INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

  where   objectproperty( object_id( constraint_name ), 'IsPrimaryKey' ) = 1

  Note that the information_schema views only returns info on objects that the executing user has permissions on.

  Also note that all methods to find PK names only works if there are declared primary key constraints - it won't work if you have columns acting as PK's but perhaps only have an uinque index placed on them.

  /Kenneth

• Michael Du Bois

  SSCertifiable

  Points: 5163

  More actions

  May 16, 2005 at 4:57 pm

  #559149

  Try using sp_helpIndex.

  HTH Mike

  use Northwind

   

  EXEC sp_helpindex 'employees'

  /*

  Returns

  LastName nonclustered located on PRIMARY LastName

  PK_Employees clustered, unique, primary key located on PRIMARY EmployeeID

  PostalCode nonclustered located on PRIMARY PostalCode

  */