In SQL Server 2005, can I do a cascade delete without setting the property on my tables?

Combining your advice and a script I found online, I made a procedure that will produce SQL you can run to perform a cascaded delete regardless of ON DELETE CASCADE It was probably a big waste of time, but I had a good time writing it. An advantage of doing it this way is, you can put a GO statement between each line, and it doesn't have to be one big transaction. The original was a recursive procedure; this one unrolls the recursion into a stack table create procedure usp_delete_cascade ( @base_table_name varchar(200), @base_criteria nvarchar(1000) ) as begin -- Adapted from sqlteam.com/article/performing-a-cascade... -- 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.

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 select 'print ''deleting from ' + table_name + '...''; delete from ' + table_name + ' where ' + criteria from @to_delete order by id desc end exec usp_delete_cascade 'root_table_name', 'id = 123.

Combining your advice and a script I found online, I made a procedure that will produce SQL you can run to perform a cascaded delete regardless of ON DELETE CASCADE. It was probably a big waste of time, but I had a good time writing it. An advantage of doing it this way is, you can put a GO statement between each line, and it doesn't have to be one big transaction.

The original was a recursive procedure; this one unrolls the recursion into a stack table. Create procedure usp_delete_cascade ( @base_table_name varchar(200), @base_criteria nvarchar(1000) ) as begin -- Adapted from sqlteam.com/article/performing-a-cascade... -- 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. 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 select 'print ''deleting from ' + table_name + '...''; delete from ' + table_name + ' where ' + criteria from @to_delete order by id desc end exec usp_delete_cascade 'root_table_name', 'id = 123.

Nice script! Thank you! – splattne Jun 15 '09 at 9:17 would this script allow me to send in for instance 'code = ABC AND name = dave' – ThePower Apr 28 at 8:32 It's been awhile, but I think so!

– Kevin Conner Apr 29 at 23:03.

Unless you want to maintain all related queries as proposed by Chris, the ON DELETE CASCADE is by far the quickest and the most direct solution. And if you don't want it to be permanent, why don't you have some T-SQL code that will switch this option on and off like here remove the original Tbl_A_MyFK constraint (without the ON DELETE CASCADE) ALTER TABLE Tbl_A DROP CONSTRAINT Tbl_A_MyFK set the constraint Tbl_A_MyFK with the ON DELETE CASCADE ALTER TABLE Tbl_A ADD CONSTRAINT Tbl_A_MyFK FOREIGN KEY (MyFK) REFERENCES Tbl_B(Column) ON DELETE CASCADE Here you can do your delete DELETE FROM Tbl_A WHERE ... drop your constraint Tbl_A_MyFK ALTER TABLE Tbl_A DROP CONSTRAINT Tbl_A_MyFK set the constraint Tbl_A_MyFK without the ON DELETE CASCADE ALTER TABLE Tbl_A ADD CONSTRAINT Tbl_A_MyFK FOREIGN KEY (MyFK) REFERENCES (Tbl_B).

Go into SQL Server Management Studio and right-click the database. Select Tasks->Generate Scripts. Click Next twice.

On the Options window choose set it to generate CREATE statements only, and put everything to False except for the Foreign Keys. Click Next. Select Tables and Click Next again.

Click the "Select All" button and click Next then Finish and send the script to your choice of a query window or file (don't use the clipboard, since it might be a big script). Now remove all of the script that adds the tables and you should be left with a script to create your foreign keys. Make a copy of that script because it is how you'll restore your database to its current state.

Use a search and replace to add the ON DELETE CASCADE to the end of each constraint. This might vary depending on how your FKs are currently set up and you might need to do some manual editing. Repeat the script generation, but this time set it to generate DROP statements only.Be sure to manually remove the table drops that are generated.

Run the drops, then run your edited creates to make them all cascade on delete. Do your deletes, run the drop script again and then run the script that you saved off at the start. Also - MAKE A BACKUP OF YOUR DB FIRST!

Even if it's just a dev database, it will save you some headache if part of the script isn't quite right. Hope this helps! BTW - you should definitely do some testing with your full test data as another poster suggested, but I can see why you might not need that for initial development.

Just don't forget to include that as part of QA at some point.

I usually just hand write the queries to delete the records I don't want and save that as a . Sql file for future reference. The pseudocode is: select id's of records from the main table that I want to delete into a temp table write a delete query for each related table which joins to the temp table.

Write a delete query for the main table joining to my temp table.

My suggestion is to go ahead and write a script that will add the on delete cascade to each relationship in the database while exporting a list of modified relationships. Then you can reverse the process and remove the on delete cascade command on each table in the list.

Personally if you are going to leave the records in production, I would also leave them in development. Otherwise you may write code that works fine when the recordset is small but times out when faced with the real recordset. But if you are determined to do this, I would copy the id field of the records you want to dete from the main table first to a work table.

Then I would take each related table and write a delete joining to that worktable to only delete those records. Finish up with the parent table. Make sure this ia written in a script and saved so the next time you want to do a similar thing to your test data, you can easily run it without having to figure out what are the reated tables that need records deleted from them.

After select you have to build and execute the actual delete declare @deleteSql nvarchar(1200) declare delete_cursor cursor for select table_name, criteria from @to_delete order by id desc open delete_cursor fetch next from delete_cursor into @table_name, @criteria while @@fetch_status = 0 begin select @deleteSql = 'delete from ' + @table_name + ' where ' + @criteria --print @deleteSql -- exec sp_execute @deleteSql EXEC SP_EXECUTESQL @deleteSql fetch next from delete_cursor into @table_name, @criteria end close delete_cursor deallocate delete_cursor.

Add this after select statement – dan Mar 31 at 20:42.

Taking the accepted answer a bit further, I had the need to do this across tables in different schemas. I have updated the script to include schema in the outputted delete scripts. CREATE PROCEDURE usp_delete_cascade ( @base_table_schema varchar(100), @base_table_name varchar(200), @base_criteria nvarchar(1000) ) as begin -- 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.

Declare @to_delete table ( id int identity(1, 1) primary key not null, criteria nvarchar(1000) not null, table_schema varchar(100), table_name varchar(200) not null, processed bit not null, delete_sql varchar(1000) ) insert into @to_delete (criteria, table_schema, table_name, processed) values (@base_criteria, @base_table_schema, @base_table_name, 0) declare @id int, @criteria nvarchar(1000), @table_name varchar(200), @table_schema varchar(100) while exists(select 1 from @to_delete where processed = 0) begin select top 1 @id = id, @criteria = criteria, @table_name = table_name, @table_schema = table_schema from @to_delete where processed = 0 order by id desc insert into @to_delete (criteria, table_schema, table_name, processed) select referencing_column. Name + ' in (select ' + referenced_column. Name + ' from ' + @table_schema + '.' + @table_name +' where ' + @criteria + ')', schematable.

Name, 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. Schemas schematable on referencing_table. Schema_id = schematable.

Schema_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 select 'print ''deleting from ' + table_name + '...''; delete from ' + table_schema + '.' + table_name + ' where ' + criteria from @to_delete order by id desc end exec usp_delete_cascade 'schema', 'RootTable', 'Id = 123' exec usp_delete_cascade 'schema', 'RootTable', 'GuidId = ''A7202F84-FA57-4355-B499-1F8718E29058.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.