How to list the column name(s) which are Primary key(s) of any table?

• Joy Dubey

  SSC Journeyman

  Points: 83

  More actions

  November 7, 2005 at 8:33 am

  #102910

  Hi,

   

  Aim: To list the column name(s) which are Primary key(s) of any table.

   

  I came up with a query.

   

  1) Could please anyone review it and confirm it?

  2) Please feel free to tailor to make it simpler and efficent.

   

  SELECT        COLUMN_NAME

  FROM           INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

  WHERE         TABLE_NAME = '<<TABLE_NAME>>' 

                  AND

                      CONSTRAINT_NAME =

                      (      SELECT    CONSTRAINT_NAME

                              FROM      INFORMATION_SCHEMA.TABLE_CONSTRAINTS

                              WHERE   TABLE_NAME = '<<TABLE_NAME>>'

                                          AND

                                            CONSTRAINT_TYPE = 'PRIMARY KEY' )

   

  Regards

  Joy

• rsharma

  SSCarpal Tunnel

  Points: 4865

  More actions

  November 7, 2005 at 10:40 am

  #602641

  In case your primary key has more than one column, you would be interested in the column order as well..you can use this:

  SELECT

  COLS.CONSTRAINT_NAME,COLS.COLUMN_NAME,COLS.ORDINAL_POSITION

  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COLS

   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS

    ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME

  WHERE COLS.CONSTRAINT_CATALOG = DB_NAME()

  AND COLS.TABLE_NAME = 'XXXX'

  AND CONS.CONSTRAINT_TYPE = 'PRIMARY KEY'

  ORDER BY COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION

  You can replace the name of the table with whatever you want and also replace the constraint_type if you want to search for constraints other than the Primary Key.

• Indu Jakhar

  Old Hand

  Points: 337

  More actions

  November 8, 2005 at 2:03 am

  #602733

  Hi!

  Below is the query that will give you the primary key constraint names and the columns used in them for all the user created tables in your database.

  SELECT OBJ.NAME, COLS.CONSTRAINT_NAME,COLS.COLUMN_NAME,COLS.ORDINAL_POSITION

  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COLS, INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS,

  SYSOBJECTS OBJ

  WHERE COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME

  AND COLS.TABLE_NAME = OBJ.NAME

  AND CONS.CONSTRAINT_TYPE = 'PRIMARY KEY'

  AND OBJ.TYPE = 'U'

  ORDER BY OBJ.NAME, COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION

   

  ---

  Best Regards,

  Indu Jakhar

• Johan Bijnens

  SSC Guru

  Points: 135550

  More actions

  November 8, 2005 at 3:48 am

  #602755

  From Books online :

  sp_pkeys

  Returns primary key information for a single table in the current environment.

  Syntax

  sp_pkeys [ @table_name = ] 'name'

      [ , [ @table_owner = ] 'owner' ]

      [ , [ @table_qualifier = ] 'qualifier' ]

   

  

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me