Hello All,
Very recent I found requirement for removing parent record which is having tons of relation as they have foreign key constrains; you can’t delete them unless you have ON CASCADE DELETE
I found solution on Stack Overflow and base main article from sqlteam. I have modified procedure to fix some minor issue and here is the procedure which take table name and a query to filter row which we need to delete from table along with its reference.
-- ================================================
-- Expects the name of a table, and a conditional for selecting rows
-- within that table that you want deleted.
-- Produces SQL that, when run, deletes all table rows referencing the ones
-- you initially selected, cascading into any number of tables,
-- without the need for "ON DELETE CASCADE".
-- Does not appear to work with self-referencing tables, but it will
-- delete everything beneath them.
-- To make it easy on the server, put a "GO" statement between each line.
-- ================================================
CREATE PROCEDURE DeleteCascade (
@BaseTableName VARCHAR(200)
,@BaseCriteria VARCHAR(1000)
)
AS BEGIN
DECLARE @ToDelete TABLE
(
Id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
Criteria VARCHAR(5000) NOT NULL,
TableName VARCHAR(200) NOT NULL,
Processed BIT NOT NULL,
DeleteSql VARCHAR(5000)
)
SET NOCOUNT ON
INSERT INTO @ToDelete (Criteria,TableName , Processed)
VALUES (@BaseCriteria, @BaseTableName, 0)
DECLARE @Id INT
DECLARE @Criteria VARCHAR(5000)
DECLARE @TableName VARCHAR(5000)
WHILE EXISTS(SELECT 1 FROM @ToDelete WHERE Processed = 0)
BEGIN
SELECT TOP 1
@Id = Id
,@Criteria = Criteria
,@TableName = TableName
FROM @ToDelete
WHERE Processed = 0
ORDER BY Id DESC
INSERT INTO @ToDelete (Criteria, TableName, Processed)
SELECT
ReferencingColumn.name + ' IN (SELECT [' + ReferencedColumn.name + '] FROM ['
+ @TableName +'] WHERE ' + @Criteria + ')',
ReferencingTable.name,0
FROM sys.foreign_key_columns fk
INNER JOIN sys.columns ReferencingColumn
ON fk.parent_object_id = ReferencingColumn.object_id
AND fk.parent_column_id = ReferencingColumn.column_id
INNER JOIN sys.columns ReferencedColumn
ON fk.referenced_object_id = ReferencedColumn.object_id
AND fk.referenced_column_id = ReferencedColumn.column_id
INNER JOIN sys.objects ReferencingTable
ON fk.parent_object_id = ReferencingTable.object_id
INNER JOIN sys.objects ReferencedTable
ON fk.referenced_object_id = ReferencedTable.object_id
INNER JOIN sys.objects constraint_object
ON fk.constraint_object_id = constraint_object.object_id
WHERE ReferencedTable.name = @TableName
AND ReferencingTable.name != ReferencedTable.name
UPDATE @ToDelete
SET Processed = 1
WHERE Id = @Id
END
SELECT
'PRINT ''Deleting from ' + TableName + '...''; DELETE FROM [' + TableName + '] WHERE '
+ Criteria
FROM @ToDelete
ORDER BY Id DESC
END
When you execute this, you will see list of DELETE statement, which you need to run.
