updateing all Foreign key constraint in sql server?

Question

I have a database in sql server 2008 R2 that have many table (over 200) and have many relation betweens tables. Delete rule in most of relations is No Action I need to Update all relation delete rule to Cascade at once Because of too many relation in my database I dont want do this one by one Is there any way ?

Answer 1

Typically, when you ALTER a Foreign key constraint, using the SSMS GUI, SQL Server on the background actually drops and recreates the same.

Here is a script, that will generate the SQL for dropping all FKeys and recreating them with ON UPDATE CASCADE ON DELETE CASCADE options

Assumption is, all your FKeys are name "FK....."

SET NOCOUNT ON;

DECLARE @Objects TABLE
(
ID int identity(1,1),
TableName sysname,
SchemaName sysname
)

INSERT INTO @Objects (TableName, SchemaName)
    SELECT
        TABLE_NAME,
        CONSTRAINT_SCHEMA
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE CONSTRAINT_NAME LIKE 'FK%' 

DECLARE @min int, @max int,@table sysname,@schema sysname

SELECT @min = 1, @max = MAX(ID) FROM @Objects

WHILE @min <=@max
BEGIN

SELECT
    @table = TableName,@schema = SchemaName FROM @Objects WHERE ID = @min

print '/*Drop Foreign Key Statements for [' + @schema + '].[' + @table + ']*/'
SELECT
    'ALTER TABLE [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + '] 
DROP CONSTRAINT [' + fk.name + ']'
    FROM sys.foreign_keys fk
    INNER JOIN sys.objects o
        ON fk.parent_object_id = o.object_id
    WHERE o.name = @table AND
    SCHEMA_NAME(o.schema_id) = @schema

    print '/*Create Foreign Key Statements for [' 
+ @schema + '].[' + @table + ']*/'
    SELECT
        'ALTER TABLE [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + '] 
ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY ([' + c.name + ']) 
    REFERENCES [' + SCHEMA_NAME(refob.schema_id) + '].[' + refob.name + ']
([' + refcol.name + '])ON UPDATE  CASCADE ON DELETE  CASCADE'
    FROM sys.foreign_key_columns fkc
    INNER JOIN sys.foreign_keys fk
        ON fkc.constraint_object_id = fk.object_id
    INNER JOIN sys.objects o
        ON fk.parent_object_id = o.object_id
    INNER JOIN sys.columns c
        ON fkc.parent_column_id = c.column_id AND
    o.object_id = c.object_id
INNER JOIN sys.objects refob
    ON fkc.referenced_object_id = refob.object_id
INNER JOIN sys.columns refcol
    ON fkc.referenced_column_id = refcol.column_id AND
    fkc.referenced_object_id = refcol.object_id
WHERE o.name = @table AND
SCHEMA_NAME(o.schema_id) = @schema

SET @min = @min+1
END

Hope this helps.

Raj

PS: Setting query output to text helps. Also please read the comment posted on your question. Arbitrarily setting CASCADE may not be the right thing to do