I eventually came up with a stored procedure that can be called to retrieve or delete records, including all child records, by using a derived table to build a tree of foreign key relationships.
Note that this procedure uses sp_executesql to dynamically execute SQL from concatenated strings, but does not perform any input validation against SQL injection attacks. It should not therefore be called directly but should be called from a utility procedure responsible for sanitizing input parameters.
The parameters passed with this stored procedure are:
| @fromTbl | The table from which to select or delete rows (supplied without qualifying owner name). |
| @whereCriteria | The WHERE criteria for the query (fully specified with table name). |
| @cmdType | 1 = SELECT or 2 = DELETE. |
| @retMsg | Output parameter. Returns numbers of rows in each table affected. |
| @retCode | Output parameter. 1 indicates record(s) seleted, 2 indicates record(s) deleted successfully, 100 indicates no matching rows, -100 indicates error. |
CREATE PROC [dbo].[procGetChildRows](
@fromTbl nvarchar(50),
@whereCriteria nvarchar(100),
@cmdType smallint,
@retMsg varchar(500) OUTPUT,
@retCode smallint OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @errCount smallint,
@fromTblOwner nvarchar(50),
@fromPK nvarchar(50),
@err int,
@foundChildTbls bit,
@init bit,
@relLevel smallint,
@parentTbls nvarchar(500),
@tblID int,
@tblIDCount int,
@parentTblOwner nvarchar(50),
@parentTbl nvarchar(50),
@tblOwner nvarchar(50),
@tblName nvarchar(50),
@fkName nvarchar(50),
@pkName nvarchar(50),
@cmdSubject nvarchar(50),
@cmd nvarchar(4000),
@prevParentTbl nvarchar(100),
@rowCount int,
@paramDef nvarchar(50)
SET @errCount = 0
IF CHARINDEX('.', @whereCriteria) = 0
SET @whereCriteria = @fromTbl + N'.' + @whereCriteria
LockTimeoutRetry:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
-- Get owner and primary key column name of top level table.
SELECT @fromTblOwner = tc.CONSTRAINT_SCHEMA,
@fromPK = kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON tc.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON rc.UNIQUE_CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE kcu.TABLE_NAME = @fromTbl
AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler
SELECT @cmd = N'SELECT @rowCount = COUNT(*) FROM ' + @fromTblOwner + N'.' + @fromTbl + N' WHERE ' + @whereCriteria,
@paramDef = N'@rowCount int OUTPUT'
EXEC sp_executesql @cmd, @paramDef, @rowCount OUTPUT
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler
IF @rowCount = 0
BEGIN
SELECT @retMsg = 'No matching rows. 0 rows affected.',
@retCode = 100
COMMIT TRANSACTION
RETURN 0
END
SELECT @foundChildTbls = 1, @init = 1, @relLevel = 0
DECLARE @delTbl TABLE (
FTblID int identity,
FTblOwner nvarchar(50),
FTblName nvarchar(50),
FKColName nvarchar(50),
PTblOwner nvarchar(50),
PTblName nvarchar(50),
PKColName nvarchar(50),
RelLevel smallint,
RowCount int)
-- Look for child tables until no more child tables found.
WHILE @foundChildTbls = 1
BEGIN
-- Increment relationship level variable.
SELECT @relLevel = @relLevel + 1
IF @init = 1
-- Initialize @parentTbls variable.
SELECT @parentTbls = N';' + @fromTbl + N';'
ELSE
BEGIN
-- Create comma separated list of child tables.
SELECT @parentTbls = ''
SELECT @parentTbls = @parentTbls + d.FTblName + N';'
FROM (
SELECT FTblName FROM @delTbl WHERE RelLevel = @relLevel - 1
) AS d
SELECT @parentTbls = N';' + @parentTbls
END
/*
Insert table owner, table name, foreign key column name, parent table owner, parent table, primary key column name and relationship level.
*/
INSERT INTO @delTbl (FTblOwner, FTblName, FKColName, PTblOwner, PTblName, PKColName, RelLevel)
SELECT CAST(ft.CONSTRAINT_SCHEMA AS nvarchar(50)),
CAST(ft.TABLE_NAME AS nvarchar(50)),
CAST(fk.COLUMN_NAME AS nvarchar(50)),
CAST(tc.CONSTRAINT_SCHEMA AS nvarchar(50)),
CAST(tc.TABLE_NAME AS nvarchar(50)),
CAST(pk.COLUMN_NAME AS nvarchar(50)),
@relLevel
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON tc.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ft ON rc.CONSTRAINT_NAME = ft.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk ON rc.UNIQUE_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fk ON rc.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
WHERE @parentTbls LIKE '%;' + tc.TABLE_NAME + ';%'
AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND ft.CONSTRAINT_TYPE = 'FOREIGN KEY'
SELECT @err = @@ERROR, @rowCount = @@ROWCOUNT
IF @err <> 0 GOTO ErrorHandler
-- If insert returns 0 rowcount, break out of loop.
IF @rowCount = 0 SELECT @foundChildTbls = 0
SELECT @init = 0
END
SELECT @tblIDCount = MAX(FTableID) FROM @delTbl
-- Start at end of @delTbl.
SET @tblID = @tblIDCount
IF @cmdType = 1
BEGIN
WHILE @tblID > 0
BEGIN
-- For each entry in @delTbl construct SELECT COUNT(PK) command.
SELECT @parentTblOwner = PTblOwner, @parentTbl = PTblName, @tblOwner = FTblOwner, @tblName = FTblName, @fkName = FKColName, @pkName = PKColName, @relLevel = RelLevel
FROM @delTbl
WHERE FTblID = @tblID
SELECT @cmdSubject = COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE kcu.TABLE_NAME = @tblName
AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler
SELECT @cmd = N'SELECT @rowCount = COUNT(' + @cmdSubject +
N') FROM ' + @tblOwner + N'.' + @tblName + N' INNER JOIN ' + @parentTblOwner + N'.' + @parentTbl + N' ON ' + @tblName + N'.' + @fkName + N' = ' + @parentTbl + N'.' + @pkName + N' '
WHILE @relLevel > 1
-- Add joins to FROM clause.
BEGIN
SELECT @prevParentTbl = @parentTbl
SELECT @parentTblOwner = PTblOwner, @parentTbl = PTblName, @tblName = FTblName, @fkName = FKColName, @pkName = PKColName
FROM @delTbl
WHERE FTblName = @prevParentTbl AND RelLevel = @relLevel - 1
SELECT @rowCount = @@ROWCOUNT
IF @rowCount > 0
SELECT @cmd = @cmd + N'INNER JOIN ' + @parentTblOwner + N'.' + @parentTbl + N' ON ' + @tblName + N'.' + @fkName + N' = ' + @parentTbl + N'.' + @pkName + N' ',
@relLevel = @relLevel - 1
ELSE BREAK
END
-- Add WHERE clause.
SELECT @cmd = @cmd + N' WHERE ' + @whereCriteria,
@paramDef = N'@rowCount int OUTPUT'
EXEC sp_executesql @command, @paramDef, @rowCount OUTPUT
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler
UPDATE @delTbl SET RowCount = @rowCount WHERE FTblID = @tblID
SET @tblID = @tblID - 1
END
SET @retMsg = ''
SELECT @retMsg = @retMsg + CAST(d.RowCount AS varchar(50)) +
' row(s) from ' + d.FTblName + ', '
FROM (SELECT TOP 100 PERCENT RowCount, FTblName
FROM @delTbl
WHERE RowCount > 0
ORDER BY FTblID
) AS d
IF LEN(@retMsg) > 0 SET @retMsg = LEFT(@retMsg, LEN(@retMsg) - 1)
SELECT TOP 1 @parentTblOwner = PTblOwner FROM @delTbl WHERE PTblName = @fromTbl
-- Add WHERE clause.
SELECT @cmd = N'SELECT @rowCount = COUNT(' + @fromPK + N') FROM ' + @parentTblOwner + N'.' + @fromTbl + N' WHERE ' + @whereCriteria,
@paramDef = N'@rowCount int OUTPUT'
EXEC sp_executesql @cmd, @paramDef, @rowCount OUTPUT
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler
IF LEN(@retMsg) > 0
SET @retMsg = CAST(@rowCount AS varchar(50)) + ' row(s) from ' + @fromTbl + ', ' + @retMsg
ELSE
SET @retMsg = CAST(@rowCount AS varchar(50)) + ' row(s) from ' + @fromTbl
SET @retCode = 1
END
IF @cmdType = 2
BEGIN
WHILE @tblID > 0
BEGIN
SELECT @parentTblOwner = PTblOwner, @ParentTbl = PTblName, @tblOwner = FTblOwner, @tblName = FTblName, @fkName = FKColName, @pkName = PKColName, @relLevel = RelLevel
FROM @delTbl
WHERE FTblID = @tblID
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler
SELECT @cmd = N'DELETE ' + @tblName + N' FROM ' + @tblOwner + N'.' + @tblName + N' INNER JOIN ' + @parentTblOwner + N'.' + @parentTbl + N' ON ' + @tblName + N'.' + @fkName + N' = ' + @parentTbl + N'.' + @pkName + N' '
WHILE @relLevel > 1
BEGIN
SELECT @prevParentTbl = @parentTbl
SELECT @parentTblOwner = PTblOwner, @parentTbl = PTblName, @tblName = FTblName, @fkName = FKColName, @pkName = PKColName
FROM @delTbl
WHERE FTblName = @prevParentTbl AND RelLevel = @relLevel - 1
SELECT @rowCount = @@ROWCOUNT
IF @rowCount > 0
SELECT @cmd = @cmd + N'INNER JOIN ' + @parentTblOwner + N'.' + @parentTbl + N' ON ' + @tblName + N'.' + @fkName + N' = ' + @parentTbl + N'.' + @pkName + N' ',
@relLevel = @relLevel - 1
ELSE BREAK
END
-- Add WHERE clause.
SELECT @cmd = @cmd + N' WHERE ' + @whereCriteria
EXEC sp_executesql @cmd
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler
SET @tblID = @tblID - 1
END
SET @cmd = N'DELETE ' + @fromTblOwner + N'.' + @fromTbl +
N' WHERE ' + @whereCriteria
EXEC sp_executesql @cmd
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler
SET @retCode = 2
END
COMMIT TRANSACTION
RETURN 0
ErrorHandler:
ROLLBACK TRANSACTION
IF @err IN (1205, 1222)
BEGIN
IF @errCount = 5
BEGIN
RAISERROR('Unable to connect to database.', 16, 1)
RETURN -100
END
WAITFOR DELAY '00:00:00.25'
SET @errCount = @errCount + 1
GOTO LockTimeoutRetry
END
ELSE
-- Unknown error
BEGIN
SET @retCode = -100
RAISERROR(@err, 16, 1) WITH LOG
RETURN -100
ENDThe @fromTbl parameter should be supplied without a qualifying owner name. If the @whereCriteria parameter includes multiple criteria, it must specify the table name with the column name.
The stored procedure first uses the TABLE_CONSTRAINTS, KEY_COLUMN_USAGE and REFERENTIAL_CONSTRAINTS ANSI standard views to retrieve the owner and primary key column name of the table specified by the @fromTbl parameter. It then checks whether there are any rows in the table that satisfy the WHERE criteria supplied by the @whereCriteria parameter. If there are none, it exits with the appropriate values in @RetMsg and @RetCode parameters. If it finds rows, it proceeds to query the TABLE_CONSTRAINTS, REFERENTIAL_CONSTRAINTS and KEY_COLUMN_USAGE views for child tables and stores information about the child tables in a table variable, including information about the hierarchy. It does this for all the table names it has stored in the table variable. Once it has finished constructing its table of child tables, it starts at the end of the table and works its way up the levels to construct a SELECT COUNT or DELETE command.
The procedure wraps its SQL DML commands in a transaction. If any error occurs, it will roll back the transaction.
Note that this procedure does not support the following foreign key relationships in the database schema:
- Recursive foreign key relationship on a table.
- Cyclical foreign key relationships on two or more tables.
