Script to ID foreign keys referencing a table

• PK-256781

  Old Hand

  Points: 382

  More actions

  November 2, 2005 at 6:49 am

  #89724

  Hi,

  Perhaps someone has already written this script.

  Based on a table name, I would like to identify details of the foreign keys (Table name, column names) that reference the table.

  Thanks.

  Regards,

  PK.

• rsharma

  SSCarpal Tunnel

  Points: 4865

  More actions

  November 2, 2005 at 7:21 am

  #601825

  SELECT  (CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED' ELSE 'DISABLED' END) AS STATUS,        

  OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,       

  OBJECT_NAME(FKEYID) AS TABLE_NAME,        

  COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,       

  OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,       

  COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME

  FROM SYSFOREIGNKEYS

  WHERE OBJECT_NAME(RKEYID) = 'your table name here'

  ORDER BY TABLE_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME,  KEYNO

  GO

   

• PK-256781

  Old Hand

  Points: 382

  More actions

  November 2, 2005 at 9:31 am

  #601896

  Thanks rsharma.  Your help is much appreciated.

  Regards,

  PK.