TSQL Search foreign keys

TSQL-SearchForeignKeys.md

The following is an example on how to search Foreign Keys using TSQL

Tested using MS SQL Server 2000-2017

List Foreign Key Constraints

-- List Foreign Key Constraints on table. Ex: D_TRANS_ACCOUNT_ENTRY
DECLARE @tableName sysname
SET @tableName = '...' -- Your table name goes here
SELECT
  t.name as 'FK Table', c.name as 'FK Column Name', target.name as 'On Table', targetc.name as 'On Column'
FROM
  sysobjects t                              -- source table
  INNER JOIN syscolumns c ON t.id = c.id    -- source column
  INNER JOIN sysconstraints co ON t.id = co.id AND co.colid = c.colid   -- general constraint
  INNER JOIN sysforeignkeys fk ON co.constid = fk.constid               -- foreign key constraint
  INNER JOIN sysobjects target ON fk.rkeyid = target.id                 -- target table
  -- target column
  INNER JOIN syscolumns targetc ON fk.rkey = targetc.colid AND fk.rkeyid = targetc.id
WHERE
  t.name = @tableName         -- FKs this table has
  -- target.name = @tableName -- Tables that have a FK against this one

List all FK Constraints

-- Show ALL FK Constraints
SELECT
  t.name as 'FK Table', c.name as 'FK Column Name', target.name as 'On Table', targetc.name as 'On Column'
FROM
  sysobjects t                              -- source table
  INNER JOIN syscolumns c ON t.id = c.id    -- source column
  INNER JOIN sysconstraints co ON t.id = co.id AND co.colid = c.colid   -- general constraint
  INNER JOIN sysforeignkeys fk ON co.constid = fk.constid               -- foreign key constraint
  INNER JOIN sysobjects target ON fk.rkeyid = target.id                 -- target table
  -- target column
  INNER JOIN syscolumns targetc ON fk.rkey = targetc.colid AND fk.rkeyid = targetc.id
order by t.name asc, c.name