Find Foreign Keys

• suba.sathyanathan 40131

  SSC Enthusiast

  Points: 191

  More actions

  September 10, 2012 at 4:13 pm

  #261308

  Comments posted to this topic are about the item Find Foreign Keys

• dbasnyat25

  Newbie

  Points: 8

  More actions

  September 19, 2012 at 3:02 pm

  #1539801

  soory i am getting an error

• suba.sathyanathan 40131

  SSC Enthusiast

  Points: 191

  More actions

  September 20, 2012 at 11:34 pm

  #1540470

  Not sure if there's any whitespace problem. Please use the below query.

  ;With CTE As

  (Select Object_Name(constraint_object_id) Constraint_Name,

  Object_Name(parent_object_id) Table_Name,

  C.name Column_Name

  From sys.foreign_key_columns FK

  Inner Join sys.columns C

  On FK.parent_object_id = C.object_id

  And FK.parent_column_id = C.column_id)

  Select C.Constraint_Name,

  C.Table_Name,

  C.Column_Name,

  Object_Name(FK.referenced_object_id) Referenced_Table_Name,

  SC.name Referenced_Column_Name

  from CTE C

  Inner Join sys.foreign_key_columns FK

  On C.Constraint_Name = Object_Name(FK.constraint_object_id)

  Inner Join sys.columns SC

  On FK.referenced_object_id = SC.object_id

  And FK.referenced_column_id = SC.column_id

• suba.sathyanathan 40131

  SSC Enthusiast

  Points: 191

  More actions

  September 20, 2012 at 11:34 pm

  #1540471

  Not sure if there's any whitespace problem. Please use the below query.

  ;With CTE As

  (Select Object_Name(constraint_object_id) Constraint_Name,

  Object_Name(parent_object_id) Table_Name,

  C.name Column_Name

  From sys.foreign_key_columns FK

  Inner Join sys.columns C

  On FK.parent_object_id = C.object_id

  And FK.parent_column_id = C.column_id)

  Select C.Constraint_Name,

  C.Table_Name,

  C.Column_Name,

  Object_Name(FK.referenced_object_id) Referenced_Table_Name,

  SC.name Referenced_Column_Name

  from CTE C

  Inner Join sys.foreign_key_columns FK

  On C.Constraint_Name = Object_Name(FK.constraint_object_id)

  Inner Join sys.columns SC

  On FK.referenced_object_id = SC.object_id

  And FK.referenced_column_id = SC.column_id

• houming1982

  SSC Enthusiast

  Points: 110

  More actions

  October 8, 2012 at 4:37 am

  #1546635

  SELECT

  FK_Table = FK.TABLE_NAME,

  FK_Column = CU.COLUMN_NAME,

  PK_Table = PK.TABLE_NAME,

  PK_Column = PT.COLUMN_NAME,

  Constraint_Name = C.CONSTRAINT_NAME

  FROM

  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C

  INNER JOIN

  INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK

  ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME

  INNER JOIN

  INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK

  ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME

  INNER JOIN

  INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU

  ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME

  INNER JOIN

  (

  SELECT

  i1.TABLE_NAME, i2.COLUMN_NAME

  FROM

  INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1

  INNER JOIN

  INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2

  ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME

  WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’

  ) PT

  ON PT.TABLE_NAME = PK.TABLE_NAME

  ORDER BY

  FK_Column

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 9, 2016 at 1:14 pm

  #1877571

  Thanks for the script.