Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find Foreign Key Tables Expand / Collapse
Author
Message
Posted Monday, October 12, 2009 7:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:06 AM
Points: 146, Visits: 455
Comments posted to this topic are about the item Find Foreign Key Tables
Post #801897
Posted Tuesday, October 27, 2009 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 10:40 AM
Points: 4, Visits: 13
simpler statement

select object_name(referenced_object_id) as PrimaryKeyTable
,object_name(parent_object_id) as ForeignKeyTable
,*
from sys.foreign_keys
where object_name(referenced_object_id) like '%mySearchedPrimaryKeyTable%'
order by object_name(referenced_object_id)
,object_name(parent_object_id)
Post #809325
Posted Tuesday, October 27, 2009 12:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 29, 2013 12:05 PM
Points: 4, Visits: 51
EXEC sp_helpconstraint 'TableName' for single tables is simpler and covers all constraints.

Post #809533
Posted Wednesday, October 28, 2009 9:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 10:11 AM
Points: 5, Visits: 75
This URL even helps determine the columns involved in that Foreign Key.

http://sqlserver2000.databases.aspfaq.com/schema-how-do-i-find-all-the-foreign-keys-in-a-database.html
Post #810087
Posted Friday, December 18, 2009 12:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:00 PM
Points: 21,212, Visits: 14,910
This script is a nice starter script. What I see from the script is that it lists all of the foreign keys. After that, you are required to manually create the hierarchy of FKs and table relationships. Building that hierarchy is the time consuming piece.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #836559
Posted Tuesday, January 19, 2010 7:38 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:06 AM
Points: 27, Visits: 462
That is correct. This simple tutorial script solves the problem I had and is a sample for you to refactor and write whatever you want.

CirquedeSQLeil is going to write the recusive script that query FK hierarchy and publish here.


Jason
http://dbace.us
Post #849738
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse