How to trace row level dependency?

I开发者_C百科n case I need to change the PK of a single row from 1 to 10, for example, is there any way to trace every proc, view and function that might reference the old value?

I mean, a simple select in a proc like: select * from table where FK = 1 would break, and I'd had to look for every reference for ones in every proc and view and change them to 10 to get the system to work.

Is there any automatic way of doing this? I use SQL SERVER.

I suspect that the only way to do this correctly involves querying the database metadata - to identify all the places that use your PK as a FK, in a proc, or in a view. This is likely to be complex; fragile; and prone to error.

This is one of the (many) reasons to avoid having the PK as anything other than a system derived, meaningless value, which is not accessible to manipulation by (even) the creator/administrator. Also, under what circumstances would you have a PK hard coded in a proc or function - again a potential source of fragility in your system.

If a PK is created that is incorrect (by whatever criteria) or which needs to be changed, create a new record and copy the existing values into it. While this does not answer your query, your routines to delete or modify values in the table need to know how and where it is used; and so a routine to copy a row should be able to access this information.