Solved

TSQL question:  List of foreign keys in tables

Posted on 2016-11-18
2
53 Views
Last Modified: 2017-01-05
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
Comment
Question by:Tom Knowlton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41893864
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
 
LVL 5

Author Closing Comment

by:Tom Knowlton
ID: 41896265
Great!  Thanks!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Normally after a failure of Domain Controller, when promoting new DC the DC is renamed, we will discuss the options in Dcpromo to re-create the DC with the same name. Scenario: You are a small IT shop with two Domain Controllers (Domain Contr…
We recently had an issue where out of nowhere, end users started indicating that their logins to our terminal server were just showing a "blank screen." After checking the usual suspects -- profiles, shell=explorer.exe in the registry, userinit.exe,…
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question