February 18, 2008 at 9:41 am
Hi
Can some one point me in the right direction .. I need to write a snippet of code to tell me the status of all FK's in the db, are they enabled, disabled and do they have cascade delete enabled ?
many thanks
si
February 18, 2008 at 9:51 am
Simon_Lo (2/18/2008)
HiCan some one point me in the right direction ..
http://qa.sqlservercentral.com/articles/Advanced/foreignkeys/2423/
states:
-- To check whether the FK is enabled or NOT?
SELECT OBJECTPROPERTY(object_id('myforeignkey'), 'CnstIsDisabled')
-- To check whether the Table has FK?
SELECT OBJECTPROPERTY ( object_id('eRisk_UserRole'),'TableHasForeignKey')
-- To check whether the Table has PK?
SELECT OBJECTPROPERTY ( object_id('eRisk_UserRole'),'TableHasPrimaryKey')
That should get you in the direction, right?
-- Cory
February 18, 2008 at 9:59 am
well at least if use your first example with a cursor I can step through every FK in the database.. is there a flag in a system table that will show me if cascade delete is enabled ?
cheers Si
February 19, 2008 at 2:23 am
While doing checks on FK's, don't forget to also check if it's trustworthy or not..
select OBJECTPROPERTY(object_id(myForeignKey), 'CnstIsNotTrusted')
/Kenneth
February 19, 2008 at 3:11 am
Kenneth Wilhelmsson (2/19/2008)
While doing checks on FK's, don't forget to also check if it's trustworthy or not..select OBJECTPROPERTY(object_id(myForeignKey), 'CnstIsNotTrusted')
/Kenneth
Kenneth has an excellent point. Many people disable their foreign keys, and do not enable them properly and leave them in untrusted mode. <Shameless plug>I summarized this on http://www.simple-talk.com/sql/database-administration/foreign-keys-and-their-states/</Shameless plug>
Regards,
Andras
February 20, 2008 at 8:04 am
For the delete constraint try:
select constraint_name, delete_rule
from information_schema.referential_constraints
where constraint_name = [the foreign key name]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply