Different from ORACLE and MySQL, MS SQL Server does not provide Cascade delete function natively. This maybe considered as a safety feature to prevent unintentional data deletion, however, there are situations, that need Cascade Delete to quickly clean up data in complex referenced tables. To easy my life, this is what I wrote. It’s been working fine for my needs. Feel free to share and point out errors 🙂
CREATE PROCEDURE mysp_CascadeDelete
/*
Cascade Delete
by Kevin C. L. 2009-04-30
*/
(
@TableName varchar(50), /* name of the table where rows are to be deleted */
@Criteria nvarchar(1000), /* criteria used to delete the rows required */
@RowsAffected int=0 OUTPUT /* number of records affected by the delete */
)
As
set nocount on
declare
@ChildTab varchar(255), /* name of the child table */
@ChildCol varchar(255), /* name of the linking field on the child table */
@RefTab varchar(255), /* name of the parent table */
@RefCol varchar(255), /* name of the linking field in the parent table */
@sqlStmt nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */
@ChildCriteria nvarchar(1000), /* criteria to be used to delete
records from the child table */
@ChildRows int, /* number of rows deleted from the child table */
@ChildrenTableCounts int
/* declare the table variables to hold list of referencing children tables */
DECLARE @ChildrenTables TABLE
(
id int IDENTITY(1,1),
TableName varchar(255)
)
SET @ChildRows=0 -- initial ChildRows as 0
INSERT INTO @ChildrenTables
SELECT DISTINCT SO1.name AS Tab
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id
WHERE SO2.Name = @TableName
SET @ChildrenTableCounts=@@ROWCOUNT
IF @ChildrenTableCounts=0
BEGIN
SET @sqlStmt='DELETE ' + @TableName + ' FROM ' + @TableName + ' '+@Criteria
PRINT @sqlStmt
EXECUTE(@sqlStmt)
SET @RowsAffected=@@ROWCOUNT
PRINT CAST(@RowsAffected as varchar) + ' rows deleted from table: ' + @TableName
RETURN 0
END
DECLARE @i int, @keysCount int
SET @i=1
WHILE @i<=@ChildrenTableCounts
BEGIN
SELECT @ChildTab=TableName FROM @ChildrenTables
WHERE id=@i
DECLARE @RefTabKeys TABLE
(
id int IDENTITY(1,1),
RefTableName Varchar(255),
RefColName Varchar(255),
ParentColName varchar(255)
)
INSERT INTO @RefTabKeys
SELECT
SO1.name AS Tab,
SC1.name AS Col,
SC2.name AS RefCol
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id
AND FK.fkey = SC1.colid
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id
AND FK.rkey = SC2.colid
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id
WHERE SO2.Name = @TableName AND SO1.name=@ChildTab
SET @keysCount=@@ROWCOUNT
DECLARE @j int, @xCriteria varchar(1000)
SET @xCriteria=''
SET @j=1
WHILE @j<=@keysCount
BEGIN
DECLARE
@RefTableName Varchar(255),
@RefColName Varchar(255),
@ParentColName varchar(255)
SELECT @RefTableName=RefTableName,@RefColName=RefColName,@ParentColName=ParentColName
FROM @RefTabKeys
WHERE id=@j
IF @@ROWCOUNT=1
SET @xCriteria=@xCriteria+@RefTableName+'.'+@RefColName+' = '+@TableName+'.'+@ParentColName+CASE WHEN @j=@keysCount THEN ' ' ELSE ' AND ' END
SET @j=@j+1 -- do next key condition
END
SET @xCriteria=' INNER JOIN '+@TableName +' ON '+@xCriteria+' '+@Criteria
EXECUTE mysp_CascadeDelete @ChildTab, @xCriteria, @ChildRows
SET @i=@i+1 -- do next children table
END
SET @sqlStmt='DELETE '+@TableName+' FROM '+@TableName+' '+@Criteria
--PRINT @sqlStmt
EXECUTE(@sqlStmt)
SET @RowsAffected=@@ROWCOUNT
PRINT CAST(@RowsAffected as varchar) +' rows deleted from table: '+@TableName
RETURN 0