This script deletes from the source table and rows in other tables linked by foreign keys.
EXEC uspCascadeDelete
@ParentTableId = 'dbo.User',
@WhereClause = 'User.UserID = 1'
EXEC uspCascadeDelete
@ParentTableId = 'dbo.Product',
@WhereClause = 'Product.Price > 100'
@ExecuteDelete = 'Y'
This stored procedure by Aasim Abdullah is awesome for databases where cascade delete has not been set on the foreign keys. You give it the table and conditions and it will find and remove all rows from other tables linked by foreign keys.
The full procedure create script is below, usage examples at the top.
IF OBJECT_ID('dbo.udfGetFullQualName') IS NOT NULL
DROP FUNCTION dbo.udfGetFullQualName;
GO
CREATE FUNCTION dbo.udfGetFullQualName
(@ObjectId INT)
RETURNS VARCHAR (300)
AS
BEGIN
DECLARE @schema_id AS BIGINT;
SELECT @schema_id = schema_id
FROM sys.tables
WHERE object_id = @ObjectId;
RETURN '[' + SCHEMA_NAME(@schema_id) + '].[' + OBJECT_NAME(@ObjectId) + ']';
END
GO
IF OBJECT_ID('dbo.udfGetOnJoinClause') IS NOT NULL
DROP FUNCTION dbo.udfGetOnJoinClause;
GO
CREATE FUNCTION dbo.udfGetOnJoinClause
(@fkNameId INT)
RETURNS VARCHAR (1000)
AS
BEGIN
DECLARE @OnClauseTemplate AS VARCHAR (1000);
SET @OnClauseTemplate = '[<@pTable>].[<@pCol>] = [<@cTable>].[<@cCol>] AND ';
DECLARE @str AS VARCHAR (1000);
SET @str = '';
SELECT @str = @str + REPLACE(REPLACE(REPLACE(REPLACE(@OnClauseTemplate, '<@pTable>', OBJECT_NAME(rkeyid)), '<@pCol>', COL_NAME(rkeyid, rkey)), '<@cTable>', OBJECT_NAME(fkeyid)), '<@cCol>', COL_NAME(fkeyid, fkey))
FROM dbo.sysforeignkeys AS fk
WHERE fk.constid = @fkNameId;
RETURN LEFT(@str, LEN(@str) - LEN(' AND '));
END
GO
IF OBJECT_ID('dbo.uspCascadeDelete') IS NOT NULL
DROP PROCEDURE dbo.uspCascadeDelete;
GO
CREATE PROCEDURE dbo.uspCascadeDelete
@ParentTableId VARCHAR (300), @WhereClause VARCHAR (2000), @ExecuteDelete CHAR (1)='N',
@FromClause VARCHAR (8000)='', @Level INT=0
AS
SET NOCOUNT ON;
DECLARE @DebugMsg AS VARCHAR (4000),
@DebugIndent AS VARCHAR (50);
SET @DebugIndent = REPLICATE('---', @@NESTLEVEL) + '> ';
IF ISNUMERIC(@ParentTableId) = 0
BEGIN
IF CHARINDEX('.', @ParentTableId) = 0
SET @ParentTableId = OBJECT_ID('[dbo].[' + @ParentTableId + ']');
ELSE
SET @ParentTableId = OBJECT_ID(@ParentTableId);
END
IF @Level = 0
BEGIN
PRINT @DebugIndent + ' **************************************************************************';
PRINT @DebugIndent + ' *** Cascade delete ALL data from ' + dbo.udfGetFullQualName(@ParentTableId);
IF @ExecuteDelete = 'Y'
PRINT @DebugIndent + ' *** @ExecuteDelete = Y *** deleting data...';
ELSE
PRINT @DebugIndent + ' *** Cut and paste output into another window and execute ***';
END
DECLARE @CRLF AS CHAR (2);
SET @CRLF = CHAR(13) + CHAR(10);
DECLARE @strSQL AS VARCHAR (4000);
IF @Level = 0
SET @strSQL = 'SET NOCOUNT ON' + @CRLF;
ELSE
SET @strSQL = '';
SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + '''';
IF @ExecuteDelete = 'Y'
EXECUTE (@strSQL);
ELSE
PRINT @strSQL;
DECLARE curs_children CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT constid AS fkNameId,
fkeyid AS cTableId
FROM dbo.sysforeignkeys AS fk
WHERE fk.rkeyid <> fk.fkeyid
AND fk.rkeyid = @ParentTableId;
OPEN curs_children;
DECLARE @fkNameId AS INT,
@cTableId AS INT,
@cColId AS INT,
@pTableId AS INT,
@pColId AS INT;
FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId;
DECLARE @strFromClause AS VARCHAR (1000);
DECLARE @nLevel AS INT;
IF @Level = 0
BEGIN
SET @FromClause = 'FROM ' + dbo.udfGetFullQualName(@ParentTableId);
END
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @strFromClause = @FromClause + @CRLF + ' INNER JOIN ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + ' ON ' + dbo.udfGetOnJoinClause(@fkNameId);
SET @nLevel = @Level + 1;
EXECUTE dbo.uspCascadeDelete @ParentTableId = @cTableId, @WhereClause = @WhereClause, @ExecuteDelete = @ExecuteDelete, @FromClause = @strFromClause, @Level = @nLevel;
SET @strSQL = 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + @strFromClause + @CRLF + 'WHERE ' + @WhereClause + @CRLF;
SET @strSQL = @strSQL + 'PRINT ''---' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF + @CRLF;
IF @ExecuteDelete = 'Y'
EXECUTE (@strSQL);
ELSE
PRINT @strSQL;
FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId;
END
IF @Level = 0
BEGIN
SET @strSQL = @CRLF + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + ' TOP LEVEL PARENT TABLE''' + @CRLF;
SET @strSQL = @strSQL + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' WHERE ' + @WhereClause + @CRLF;
SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF;
IF @ExecuteDelete = 'Y'
EXECUTE (@strSQL);
ELSE
PRINT @strSQL;
END
CLOSE curs_children;
DEALLOCATE curs_children;