How to Find Foreign Keys in Arrays That Contain an Arbitrary Column Name

advertisements

I would like to find a set of tables which contain:

  • a foreign key constraint to a specific table, and
  • another arbitrary column name.

I'm using:

EXEC sp_fkeys 'MyTable'

This returns all of tables with foreign key constraints to 'MyTable', but I would like to do additional filtering.

As I have a particularly large list of resulting tables to work with in my database, I would like to filter the FKTABLE_NAME by tables that contain an arbitrary column name, for example CreatedOn, which is not necessarily itself the linked column.


Something like this will look for tables that contain a FK to "yourtablename" where the referencing table has a column "yourcolumnname"

SELECT
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
   ,*
FROM
   sys.foreign_keys AS f
INNER JOIN
   sys.foreign_key_columns AS fc
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
   sys.tables t
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE
   OBJECT_NAME (f.referenced_object_id) = 'yourtablename'
   AND EXISTS (SELECT 1 FROM sys.columns c WHERE c.name='yourcolumnname' AND c.object_id=f.parent_object_id)