DELETE from Parent and all its child tables with Foriegn Key (no DELETE ON CASCADE) – MSDN TSQL forum
I have a database with many tables. I would like to Delete all rows with practiceID=55 from all Parents tables and all corresponding rows from its child tables. Tables are linked with foreign key constraints (but there is no ON DELETE CASCADE).
Can any one tell how to write a generalized code for removing rows from both parent and child tables.
Query should pick parent table one by one and delete rows with practiceID=55 and all corresponding rows from its child tables.
–> My Answer:
I this case you need to create query by using system catalog tables/views, link.
Check this query below:
DECLARE @Deletestr VARCHAR(max) = '' SELECT @Deletestr = @Deletestr + 'DELETE FROM ' + object_name(fk.referenced_object_id) + 'WHERE ParentId = 55 GO ' from sys.foreign_keys fk join sys.foreign_key_columns fc on fk.object_id = fc.constraint_object_id where object_name(fk.parent_object_id) = 'SalesOrderHeader' PRINT (@Deletestr) EXEC (@Deletestr)
Like above query get the linked tables (FK) info, and finally with the output create Dynamic DELETE statement.