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:
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 )