Scripting Primary Keys

• Adam Hardy

  SSC Enthusiast

  Points: 115

  More actions

  March 2, 2005 at 4:47 pm

  #88146

  G'day all.

  MS-SQL Server

  I'm trying to work out how to get the columns used within a table level primary key constraint? As the constraints.colid is 0 for table level constraints

  I know I'm missing something obvious here but I can't find the link.

  Any ideas?

  Adam.

• wz700

  SSCertifiable

  Points: 7133

  More actions

  March 2, 2005 at 5:30 pm

  #545073

  select COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a

   where exists(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS b where CONSTRAINT_TYPE = 'PRIMARY KEY'

   and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME)

   and TABLE_NAME = 'your table name'

• Adam Hardy

  SSC Enthusiast

  Points: 115

  More actions

  March 2, 2005 at 6:03 pm

  #545077

  Thanks Old Hand, your a godsend.

  I actually saw that reference but I have never been able to work out the SQL syntax to all those functions, so you've taught me more than you know!

  Thanks again for the timely response.

  Adam