Find Foreign Key Tables

Question

Post reply

Find Foreign Key Tables

Repriser-991084

Old Hand

Points: 396
More actions
October 12, 2009 at 7:10 pm

#215444

Comments posted to this topic are about the item Find Foreign Key Tables

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Norbert L. Muth, M.A. SSC Enthusiast Points: 112 More actions · Answer 1

simpler statement

select object_name(referenced_object_id) as PrimaryKeyTable

,object_name(parent_object_id) as ForeignKeyTable

,*

fromsys.foreign_keys

whereobject_name(referenced_object_id) like '%mySearchedPrimaryKeyTable%'

order by object_name(referenced_object_id)

,object_name(parent_object_id)

Chip Broecker Grasshopper Points: 24 More actions · Answer 2

EXEC sp_helpconstraint 'TableName' for single tables is simpler and covers all constraints.

asnandrey Grasshopper Points: 17 More actions · Answer 3

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

jswong05 Hall of Fame Points: 3553 More actions · Answer 5

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
😛