StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <pre><code>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''' </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload