April 2, 2010 at 9:23 am
Hello:
We have a database with multiple tables with primary foreign constraints defined. For some of the tables, the FKs have been defined with delete rule set to Cascade. However for some, the rule was not set. My task is to identify the tables that have FKs defined with the primary table, see if the delete rule was set to Cacade, ALTER the rule to Cascade if it is not set. I could do it for one or two tables manually, but I would like to know if you have better method (preferably a script) that can do the work for me.
TIA
April 4, 2010 at 10:49 pm
Buddy, thats a huge requirement.
1. Find out the who are FKs
2. Check for the Cascade rule.
3. Create a script to compare table in this relationship
4. Create a set-based code to delete data
5. To top them all, keep the data clean.
Mate, this is huge and without any table structures (CREATE TABLE scripts), sample data (INSERT INTO scritps), Constraints (FK relations) we cannot move further.. the preparation of data in itself will take ages to do..Do us a favor, we pay back you fruitfully 🙂
April 5, 2010 at 6:25 am
In my experience the person who designs the database may have good reasons for cascaded updates and deletes on some relationships and not others...
April 5, 2010 at 8:06 am
This is not a complete answer but it may get you started, it is the minimum to assist you, but it will list all FK and related tables. (This is NOT my work, but a forum entry I found, copied and use -- my apologies to the autor for failing to include his/her name)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ListForeignKeyConstraints]
AS
SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO
You may also want to look at this forum postings
http://www.sqlservercentral.com/Forums/Topic894807-146-1.aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply