DougHughes.net

I like!

LikeLike

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

LikeLike

Excellent work.

LikeLike

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”;

LikeLike

Some lines seem to be missing in the cascadeDelete stored procedure source code displayed above…

Cheers!

JF

LikeLike

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

LikeLike