Sproc to Cascade Deletes In SQL Server
As a developer, from time to time you need to delete an record out of a database. And, if that record has a number of tables that refer to it by foreign key (and heaven forbid those tables are themselves referenced), deleting a single row can be quite a headache. This wouldn’t be so bad if your foreign keys were set to cascade deletes, but they rarely are. In these cases you’re pretty much stuck writing a lot of SQL to delete one single record.
For situations like this I’ve created a stored procedure which will automatically cascade delete records for you. Here’s the complete sproc:
1: CREATE PROCEDURE cascadeDelete
2: @table varchar(100),
3: @column varchar(100),
4: @value int
5: AS
6:
7: Print 'Need to delete from ' + @table + ' where ' + @column + '=' + convert(varchar, @value)
8:
9: DECLARE @refrencingTable varchar(100)
10: DECLARE @refrencingColumn varchar(100)
11: DECLARE @refrencingTableKey varchar(100)
12:
13: DECLARE @sql varchar(4000)
14: DECLARE @keyval int
15:
16: BEGIN TRY
17: SET @sql = 'delete from ' + @table + ' where ' + @column + '=' + convert(varchar, @value)
18: EXEC (@sql)
19: PRINT 'Bulk delete worked'
20: END TRY
21: BEGIN CATCH
22: -- first, find all the objects which refer to this object
23: DECLARE ref CURSOR LOCAL FOR
24: SELECT DISTINCT
25: OBJECT_NAME(f.parent_object_id) AS table_name
26: , COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
27: , sc.name as table_key
28: FROM sys.foreign_keys AS f
29: INNER JOIN sys.foreign_key_columns AS fc
30: ON f.object_id = fc.constraint_object_id
31: JOIN sys.columns as sc
32: ON f.parent_object_id = sc.object_id
33: JOIN sys.indexes as i
34: on f.parent_object_id = i.object_id
35: AND i.is_primary_key = 1
36: JOIN sys.index_columns as ic
37: on i.index_id = ic.index_id
38: AND i.object_id = ic.object_id
39: AND i.is_primary_key = 1
40: AND sc.column_id = ic.column_id
41: WHERE f.referenced_object_id = OBJECT_ID(@table);
42:
43: -- loop over the referring objects
44: OPEN ref
45: FETCH NEXT FROM ref INTO @refrencingTable, @refrencingColumn, @refrencingTableKey
46: WHILE @@FETCH_STATUS = 0
47: BEGIN
48: --EXEC cascadeDelete @refrencingTable, @refrencingColumn, @value
49: print @table + ' is referenced by ' + @refrencingTable + '.' + @refrencingColumn
50:
51: -- get all the id values for all the referring records and put them into a temp table
52: SET @sql = 'SELECT ' + @refrencingTableKey + ' as keyval FROM ' + @refrencingTable + ' WHERE ' + @refrencingColumn + '=' + CONVERT(varchar, @value)
53:
54: CREATE TABLE #temp (
55: keyval int
56: )
57:
This entry was posted by admin on Thursday, February 19th, 2009 at 12:00 am and is filed under SQL.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.
I like!
Nice work. This almost saved me tons of time. Seems I’m trying to delete from so many tables I get the below error after several minutes of running. I did some searching, but couldn’t find a solution.
Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Excellent work.
Good stuff, thanks!
For non-integer primary keys, what about simply put an “OR” in the where clause, and concatenate ‘…’, e.g:
“WHERE columnName = value OR columnName = ‘value’”…
I have something similar implemented in C#. As you know, it’s like a dependency tree, and you start deleting from the leaves, all the way up to the root of the tree. To get all the FK-PK reference info it needs, my code runs the following query (which I found from here:http://stackoverflow.com/questions/925738/how-to-find-foreign-key-dependencies-in-sql-server) :
string query = @”SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME,
i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE
i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
where PT.TABLE_NAME like ‘” + referencedTableName + “‘ ORDER BY K_Table ASC”;
Some lines seem to be missing in the cascadeDelete stored procedure source code displayed above…
Cheers!
JF
I was able to find the complete code in a Google cache.
Here it is:
CREATE PROCEDURE cascadeDelete
@table varchar(100),
@column varchar(100),
@value int
AS
Print ‘Need to delete from ‘ + @table + ‘ where ‘ + @column + ‘=’ + convert(varchar, @value)
DECLARE @refrencingTable varchar(100)
DECLARE @refrencingColumn varchar(100)
DECLARE @refrencingTableKey varchar(100)
DECLARE @sql varchar(4000)
DECLARE @keyval int
BEGIN TRY
SET @sql = ‘delete from ‘ + @table + ‘ where ‘ + @column + ‘=’ + convert(varchar, @value)
EXEC (@sql)
PRINT ‘Bulk delete worked’
END TRY
BEGIN CATCH
— first, find all the objects which refer to this object
DECLARE ref CURSOR LOCAL FOR
SELECT DISTINCT
OBJECT_NAME(f.parent_object_id) AS table_name
, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
, sc.name as table_key
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
JOIN sys.columns as sc
ON f.parent_object_id = sc.object_id
JOIN sys.indexes as i
on f.parent_object_id = i.object_id
AND i.is_primary_key = 1
JOIN sys.index_columns as ic
on i.index_id = ic.index_id
AND i.object_id = ic.object_id
AND i.is_primary_key = 1
AND sc.column_id = ic.column_id
WHERE f.referenced_object_id = OBJECT_ID(@table);
— loop over the referring objects
OPEN ref
FETCH NEXT FROM ref INTO @refrencingTable, @refrencingColumn, @refrencingTableKey
WHILE @@FETCH_STATUS = 0
BEGIN
–EXEC cascadeDelete @refrencingTable, @refrencingColumn, @value
print @table + ‘ is referenced by ‘ + @refrencingTable + ‘.’ + @refrencingColumn
— get all the id values for all the referring records and put them into a temp table
SET @sql = ‘SELECT ‘ + @refrencingTableKey + ‘ as keyval FROM ‘ + @refrencingTable + ‘ WHERE ‘ + @refrencingColumn + ‘=’ + CONVERT(varchar, @value)
CREATE TABLE #temp (
keyval int
)
INSERT INTO #temp
EXEC (@sql)
— loop over the table and for each row, use cascase delete to delete it.
DECLARE del CURSOR LOCAL FOR
SELECT keyval FROM #temp
OPEN del
FETCH NEXT FROM del INTO @keyval
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC cascadeDelete @refrencingTable, @refrencingTableKey, @keyval
FETCH NEXT FROM del INTO @keyval
END
CLOSE del
DEALLOCATE del
DROP TABLE #temp
FETCH NEXT FROM ref INTO @refrencingTable, @refrencingColumn, @refrencingTableKey
END
CLOSE ref
DEALLOCATE ref
SET @sql = ‘DELETE FROM ‘ + @table + ‘ WHERE ‘ + @column + ‘=’ + CONVERT(varchar, @value)
PRINT @sql
EXEC (@sql)
END CATCH