I'm doing a small bit of refactoring, however, I'd like to check to see if the current primary key I'd like to change is currently being referenced as a foreign key in any other tables. The schema I'm working with is quite large so it is not a viable option to scan through each table in the schema.
views:
37answers:
4
+3
A:
Something like this??
SELECT
fk.name,
t1.name 'Child table',
t2.name 'Parent table'
FROM
sys.foreign_keys fk
INNER JOIN
sys.tables t1 ON fk.parent_object_id = t1.object_id
INNER JOIN
sys.tables t2 ON fk.referenced_object_id = t2.object_id
WHERE
t2.name = '(your table name here)'
marc_s
2010-07-15 21:18:48
+4
A:
EXEC sp_help 'yourtable' - one of the tables in the resultset includes FKs referencing the table.
Will A
2010-07-15 21:20:58
++ great answer. Although the data is not structured, the simplicity does makes me feel a bit goldberg-esque. ;-).
Sky Sanders
2010-07-15 21:28:01
@code poet - thanks, it always helps to know how to get this stuff in vanilla table format, though - there's nothing quite like a query that returns a result set that itself can be copied + pasted into a query window and run. :)
Will A
2010-07-15 21:35:13
A:
If you would like to get this information programmatically, you can check this answer: http://stackoverflow.com/questions/2532023/getschemaforeignkeys-against-sqlclient-doesnt-yield-enough-information/2532034#2532034
Sky Sanders
2010-07-15 21:22:47
A:
Link to another possibility: http://www.sqlservercentral.com/scripts/Miscellaneous/61481/
jl
2010-07-15 21:26:35