Answered by:
Deleting from Parent and all its child tables with FK (no DELETE ON CASCADE)

Question
-
Hi Experts,
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
Thanks,
Naveen J V
Answers
-
Then I this case you need to create query by using system catalog tables/views, link: http://sqlwithmanoj.com/2010/12/06/querying-sql-server-metadata/
Query:
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.
~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 | My FB Page- Proposed as answer by Dinesh Menon Wednesday, April 8, 2015 11:00 AM
- Marked as answer by Charlie LiaoModerator Monday, April 27, 2015 3:18 AM
All replies
-
Hi Naveen.
Just to understand it better:
Delete all doctors allocated to practiceID 55,
All patients allocated to doctors that are allocated to practice55?
All records related to patients allocated to doctors that area allocated to practice 55 ?
It can destroy your but is your call.
Or delete only records from tables that have a column PracticeId ?
karepa
-
If you don't have ON DELETE CASCADE option set with FK constraint, then you can create a DELETE TRIGGER and inside this trigger delete all corresponding records from the child table.
~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 | My FB Page -
You can also do it inline in code using OUTPUT clause
http://blogs.msdn.com/b/sqltips/archive/2005/06/13/output-clause.aspx
Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page -
-
Hi Manoj,
We cannot create trigger in this point in time. Table and data already exists and I would like to cleanse the data as per the criteria mentioned.
I am asked to derive a query for cleaning the data where PracticeId=55 from all parent tables and corresponding transactions from its child.
Thanks,
Naveen
-
So, this is a one time cleanup, should be simple.
First, delete from Child table, then from the Parent table, with filter on column PracticeId=55.
Even if it reoccurring, put these 2 stmts in an SP and schedule it for every day, once a week/months.
Why do you think its complex? Can you please explain more?
~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 | My FB Page -
Manoj,
I have been given a database of more than 1500+ tables. We cannot manually identify the tables.
Script should be able to delete from all those tables in one shot. It should identify the parent table and its child with in a query and should work for all the tables.
Thanks,
Naveen J V
-
Then I this case you need to create query by using system catalog tables/views, link: http://sqlwithmanoj.com/2010/12/06/querying-sql-server-metadata/
Query:
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.
~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 | My FB Page- Proposed as answer by Dinesh Menon Wednesday, April 8, 2015 11:00 AM
- Marked as answer by Charlie LiaoModerator Monday, April 27, 2015 3:18 AM