How to Find Out Reverse FK References

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to Find Out Reverse FK References

Author

Topic

hismightiness
Posting Yak Master

164 Posts

Posted - 2010-06-07 : 10:46:10

I know that using the sp_help SProc will allow you to see the outbound FK restraints on a table. However, is there another SProc or query to find out what tables/columns are referencing the same table the other way around?

- - - -
- Will -
- - - -
http://www.willstrohl.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-07 : 13:11:24

sp_help will show foreign keys that reference that table, and tables that it references.

hismightiness
Posting Yak Master

164 Posts

Posted - 2010-06-08 : 08:43:15

Yes. I mentioned that. But I want to know what tables out there reference THIS table. For example, let's say my table has an indexed column [ProductId]. How can I find what tables are referencing that column? Further, I want to know what tables reference ANY column in a specific table.

- - - -
- Will -
- - - -
http://www.willstrohl.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-08 : 09:07:32

select * from sys.foreign_keys

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 09:21:43

or

EXEC sp_fkeys 'your_table'

Madhivanan

Failing to plan is Planning to fail

hismightiness
Posting Yak Master

164 Posts

Posted - 2010-06-08 : 09:22:20

Thanks, Peso. That's one step closer to what I am looking for, but I don't know how to match up the object ids. How can I adapt your example to check all of the columns in a given table, and tell me what tables are referencing columns in the table I am querying?

- - - -
- Will -
- - - -
http://www.willstrohl.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 09:31:17

Have you seen my previous reply?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-08 : 09:48:35

[code]-- Setup user supplied parameters
DECLARE @WantedTable SYSNAME

SET @WantedTable = 'Sales.factSalesDetail'

-- Wanted table is "parent table"
SELECT OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID(@WantedTable)

-- Wanted table is "child table"
SELECT OBJECT_SCHEMA_NAME(referenced_object_id) AS SchemaName,
OBJECT_NAME(referenced_object_id) AS TableName
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(@WantedTable)[/code]

N 56°04'39.26"
E 12°55'05.63"

hismightiness
Posting Yak Master

164 Posts

Posted - 2010-06-08 : 09:54:05

Sorry, Madhivanan. I replied before your reply appeared. That being said, I tried your example on several tables, and have yet to yield a single record on any table I have tried this query on. What gives?

- - - -
- Will -
- - - -
http://www.willstrohl.com

hismightiness
Posting Yak Master

164 Posts

Posted - 2010-06-08 : 09:57:41

I checked out the documentation for this SProc, and I have tried adding the table name to the following parameters: pktable_name, and pktable_owner

http://msdn.microsoft.com/en-us/library/ms175090.aspx

- - - -
- Will -
- - - -
http://www.willstrohl.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-08 : 09:59:03

quote:
Originally posted by hismightiness

Thanks, Peso. That's one step closer to what I am looking for, but I don't know how to match up the object ids. How can I adapt your example to check all of the columns in a given table, and tell me what tables are referencing columns in the table I am querying?

Just extend the catalog view to include columns.
See blog post here http://weblogs.sqlteam.com/peterl/archive/2010/06/08/Simple-script-to-get-referenced-table-and-their-column-names.aspx

N 56°04'39.26"
E 12°55'05.63"

hismightiness
Posting Yak Master

164 Posts

Posted - 2010-06-08 : 10:12:46

Thanks, Peso. Your subsequent reply did the trick! :)

- - - -
- Will -
- - - -
http://www.willstrohl.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources