• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • Last Modified:

TSQL question: List of foreign keys in tables

It is possible to query a database and discover all tables that use a particular foreign key?

For example, table "Entity" has a primary key called "iEntityId"

Other tables reference this column, but I don't know which ones.  I am wanting to find out programmatically.

Possible in T-SQL?
0
Tom Knowlton
Asked:
Tom Knowlton
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Try.

Use two system tables - sys.foreign_keys & sys.foreign_key_columns

SELECT OBJECT_NAME(fk.parent_object_id) tabName , 'iEntityId' colName 
FROM sys.foreign_keys fk CROSS APPLY
(
	SELECT fkc.parent_object_id,fkc.parent_column_id FROM sys.foreign_key_columns fkc 
	WHERE fk.OBJECT_ID = fkc.constraint_object_id 
	AND COL_NAME(fkc.parent_object_id,fkc.parent_column_id) = 'iEntityId'
) k 

Open in new window


Hope it helps !
0
 
Tom KnowltonWeb developerAuthor Commented:
Great!  Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now