src - http://randomconsultant.blogspot.in/2008/10/sql-server-cascade-delete.html
Delete cascade , recursive delete sqlserver
CREATE Procedure spDeleteRows /* Recursive row delete procedure. It deletes all rows in the table specified that conform to the criteria selected, while also deleting any child/grandchild records and so on. This is designed to do the same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys table to find any child tables, then deletes the soon-to-be orphan records from them using recursive calls to this procedure. Once all child records are gone, the rows are deleted from the selected table. It is designed at this time to be run at the command line. It could also be used in code, but the printed output will not be available. */ ( @cTableName varchar(50), /* name of the table where rows are to be deleted */ @cCriteria nvarchar(1000), /* criteria used to delete the rows required */ @iRowsAffected int OUTPUT /* number of records affected by the delete */ ) As set nocount on declare @cTab varchar(255), /* name of the child table */ @cCol varchar(255), /* name of the linking field on the child table */ @cRefTab varchar(255), /* name of the parent table */ @cRefCol varchar(255), /* name of the linking field in the parent table */ @cFKName varchar(255), /* name of the foreign key */ @cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */ @cChildCriteria nvarchar(1000), /* criteria to be used to delete records from the child table */ @iChildRows int /* number of rows deleted from the child table */ /* declare the cursor containing the foreign key constraint information */ DECLARE cFKey CURSOR LOCAL FOR SELECT SO1.name AS Tab, SC1.name AS Col, SO2.name AS RefTab, SC2.name AS RefCol, FO.name AS FKName FROM dbo.sysforeignkeys FK INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id AND FK.fkey = SC1.colid INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id AND FK.rkey = SC2.colid INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id WHERE SO2.Name = @cTableName OPEN cFKey FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName WHILE @@FETCH_STATUS = 0 BEGIN /* build the criteria to delete rows from the child table. As it uses the criteria passed to this procedure, it gets progressively larger with recursive calls */ SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' + @cRefTab +'] WHERE ' + @cCriteria + ')' print 'Deleting records from table ' + @cTab /* call this procedure to delete the child rows */ EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName END Close cFKey DeAllocate cFKey /* finally delete the rows from this table and display the rows affected */ SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria print @cSQL EXEC sp_ExecuteSQL @cSQL print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName
In the above scenario, we were trying to perform the following :
DELETE FROM X WHERE field1 = '234'
Using this procedure, we would use the following command:
exec spDeleteRows 'X', 'field1 = ''234''', 0
--http://stackoverflow.com/questions/116968/in-sql-server-2005-can-i-do-a-cascade-delete-without-setting-the-property-on-my
ReplyDeleteSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[Delete_cascade] (
@base_table_name varchar(200),
@base_criteria nvarchar(1000),
@debug bit = 0
)
as begin
BEGIN TRAN
BEGIN TRY
declare @to_delete table
(
id int identity(1, 1) primary key not null,
criteria nvarchar(1000) not null,
table_name varchar(200) not null,
processed bit not null,
delete_sql varchar(1000)
)
insert into @to_delete (criteria, table_name, processed)
values (@base_criteria, @base_table_name, 0)
declare @id int,
@criteria nvarchar(1000),
@table_name varchar(200)
while exists(select 1 from @to_delete where processed = 0)
begin
select top 1 @id = id, @criteria = criteria, @table_name = table_name
from @to_delete
where processed = 0
order by id desc
insert into @to_delete (criteria, table_name, processed)
select referencing_column.name + ' in (select [' + referenced_column.name + '] from [' + @table_name +'] where ' + @criteria + ')',
referencing_table.name,
0
from sys.foreign_key_columns fk
inner join sys.columns referencing_column on fk.parent_object_id = referencing_column.object_id
and fk.parent_column_id = referencing_column.column_id
inner join sys.columns referenced_column on fk.referenced_object_id = referenced_column.object_id
and fk.referenced_column_id = referenced_column.column_id
inner join sys.objects referencing_table on fk.parent_object_id = referencing_table.object_id
inner join sys.objects referenced_table on fk.referenced_object_id = referenced_table.object_id
inner join sys.objects constraint_object on fk.constraint_object_id = constraint_object.object_id
where referenced_table.name = @table_name
and referencing_table.name != referenced_table.name
update @to_delete set
processed = 1
where id = @id
end
declare @info nvarchar(250)
declare @statement nvarchar(max)
DECLARE i CURSOR FAST_FORWARD LOCAL
FOR
select 'deleting from ' + table_name + '...' as 'Info',
'delete from [' + table_name + '] where ' + criteria as 'Statement'
from @to_delete
order by id desc
OPEN i
FETCH NEXT FROM i INTO
@info,
@statement
WHILE (@@fetch_status = 0)
BEGIN
if @debug = 1
print @statement
EXEC sp_executesql @statement
FETCH NEXT FROM i INTO
@info,
@statement
END
CLOSE i
DEALLOCATE i
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT >0
ROLLBACK
--RAISERROR
END CATCH
end
GO
--TESTING
select * from Flights
select * from Pilots_on_flights
declare @p nvarchar(max)
set @p = 'id in (1,3)'
exec Delete_cascade 'Flights', @p
or
exec Delete_cascade 'table_name', ' column_name in (3)'
http://stackoverflow.com/questions/868620/sql-script-to-alter-all-foreign-keys-to-add-on-delete-cascade/868703#868703
ReplyDeletehttp://connectsql.blogspot.in/2011/03/sql-server-cascade-delete.html
ReplyDelete