August 12, 2005 at 4:07 pm
Is there a way to find out if a foreign key is cascade on delete or update?
sysforeignkeys doesn't conatin this info, neither sysobjects.
I'm tring to write a stored procedure to list all cascade foreign keys.
August 12, 2005 at 4:23 pm
Try "sp_fkeys" for a start, look in BOL for arguments and value descriptions for the reult sets.
August 12, 2005 at 11:44 pm
I think that objectproperty() can also give this information out.
August 13, 2005 at 2:02 pm
you may also want to look at the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view to see if you could create one of your own....this view uses the sysreferences, sysindexes and the sysobjects tables...
**ASCII stupid question, get a stupid ANSI !!!**
August 15, 2005 at 8:47 am
Thanks a lot guys. I'm writing a SP to find out all the circular reference tables in my DB. It will check to see if cascade delete and update is in the FKs.
i.e, if A is referenced to B, B is referenced to C, C is referenced to A. This is a circular reference. If deleting records from A, has to check if FK of B->C amd C->A have cascade rules. If there is no cascade delete and update on B->C and C->A rules, has to delete the records in B and C first.
I may use the OBJECTPROPERTY(FK_ID, 'CnstIsDeleteCascade') and OBJECTPROPERTY(FK_ID, 'CnstIsUpdateCascade') .
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply