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 http://www.sqlteam.com/article/performing-a-cascade-delete-in-sql-server-7    -- 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 descendexec usp_delete_cascade 'root_table_name', 'id = 123'

Here's a version of the accepted answer optimised for sparsely populated data models. It checks for the existence of data in a FK chain before adding it to the deletion list. I use it to clean up test data.

Don't use it in an active transactional db- it will hold locks way too long.

/*-- ============================================================================-- Purpose: Performs a cascading hard-delete.--          Not for use on an active transactional database- it holds locks for too long.--          (http://stackoverflow.com/questions/116968/in-sql-server-2005-can-i-do-a-cascade-delete-without-setting-the-property-on-my)-- eg:exec dbo.hp_Common_Delete 'tblConsumer', 'Surname = ''TestDxOverdueOneReviewWm''', 1-- ============================================================================*/create proc [dbo].[hp_Common_Delete](    @TableName sysname,     @Where nvarchar(4000),  -- Shouldn't include 'where' keyword, e.g. Surname = 'smith', NOT where Surname = 'smith'    @IsDebug bit = 0)asset nocount onbegin try    -- Prepare tables to store deletion criteria.      -- #tmp_to_delete stores criteria that is tested for results before being added to #to_delete    create table #to_delete    (        id int identity(1, 1) primary key not null,        criteria nvarchar(4000) not null,        table_name sysname not null,        processed bit not null default(0)    )    create table #tmp_to_delete     (        id int primary key identity(1,1),         criteria nvarchar(4000) not null,         table_name sysname not null    )    -- Open a transaction (it'll be a long one- don't use this on production!)    -- We need a transaction around criteria generation because we only     -- retain criteria that has rows in the db, and we don't want that to change under us.    begin tran        -- If the top-level table meets the deletion criteria, add it        declare @Sql nvarchar(4000)        set @Sql = 'if exists(select top(1) * from ' + @TableName + ' where ' + @Where + ')             insert #to_delete (criteria, table_name) values (''' + replace(@Where, '''', '''''') + ''', ''' + @TableName + ''')'        exec (@Sql)        -- Loop over deletion table, walking foreign keys to generate delete targets        declare @id int, @tmp_id int, @criteria nvarchar(4000), @new_criteria nvarchar(4000), @table_name sysname, @new_table_name sysname        while exists(select 1 from #to_delete where processed = 0)         begin            -- Grab table/criteria to work on            select  top(1) @id = id,                     @criteria = criteria,                     @table_name = table_name             from    #to_delete             where   processed = 0             order by id desc            -- Insert all immediate child tables into a temp table for processing            insert  #tmp_to_delete            select  referencing_column.name + ' in (select [' + referenced_column.name + '] from [' + @table_name +'] where ' + @criteria + ')',                    referencing_table.name            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            -- Loop on child table criteria, and insert them into delete table if they have records in the db            select @tmp_id = max(id) from #tmp_to_delete            while (@tmp_id >= 1)            begin                select @new_criteria = criteria, @new_table_name = table_name from #tmp_to_delete where id = @tmp_id                set @Sql = 'if exists(select top(1) * from ' + @new_table_name + ' where ' + @new_criteria + ')                     insert #to_delete (criteria, table_name) values (''' + replace(@new_criteria, '''', '''''') + ''', ''' + @new_table_name + ''')'                exec (@Sql)                set @tmp_id = @tmp_id - 1            end            truncate table #tmp_to_delete            -- Move to next record            update  #to_delete             set     processed = 1            where   id = @id        end        -- We have a list of all tables requiring deletion.  Actually delete now.        select @id = max(id) from #to_delete         while (@id >= 1)        begin            select @criteria = criteria, @table_name = table_name from #to_delete where id = @id            set @Sql = 'delete from [' + @table_name + '] where ' + @criteria            if (@IsDebug = 1) print @Sql            exec (@Sql)            -- Next record            set @id = @id - 1        end    commitend trybegin catch    -- Any error results in a rollback of the entire job    if (@@trancount > 0) rollback    declare @message nvarchar(2047), @errorProcedure nvarchar(126), @errorMessage nvarchar(2048), @errorNumber int, @errorSeverity int, @errorState int, @errorLine int    select  @errorProcedure = isnull(error_procedure(), N'hp_Common_Delete'),             @errorMessage = isnull(error_message(), N'hp_Common_Delete unable to determine error message'),             @errorNumber = error_number(), @errorSeverity = error_severity(), @errorState = error_state(), @errorLine = error_line()    -- Prepare error information as it would be output in SQL Mgt Studio    declare @event nvarchar(2047)    select  @event =    'Msg ' + isnull(cast(@errorNumber as varchar), 'null') +                         ', Level ' + isnull(cast(@errorSeverity as varchar), 'null') +                         ', State ' + isnull(cast(@errorState as varchar), 'null') +                         ', Procedure ' + isnull(@errorProcedure, 'null') +                         ', Line ' + isnull(cast(@errorLine as varchar), 'null') +                         ': ' + isnull(@errorMessage, '@ErrorMessage null')    print   @event    -- Re-raise error to ensure admin/job runners understand there was a failure    raiserror(@errorMessage, @errorSeverity, @errorState)end catch

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

1. remove the original Tbl_A_MyFK constraint (without the ON DELETE CASCADE)

   ALTER TABLE Tbl_A DROP CONSTRAINT Tbl_A_MyFK

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

3. Here you can do your delete

   DELETE FROM Tbl_A WHERE ...

4. drop your constraint Tbl_A_MyFK

   ALTER TABLE Tbl_A DROP CONSTRAINT Tbl_A_MyFK

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