August 5, 2013 at 8:41 am
Hi Team,
I've having 120 tables in that 80+ tables have "emp_id" as a column_name, mistakenly i've inserted a record in to Employee table,
there are many triggers on that table, it will automatically inserts records into many tables with column_name "emp_id"
now i want to delete all the records in all tables with emp_id=136,
how to delete all the records in all tables contains emp_id=136
Please suggest..!
August 5, 2013 at 8:59 am
Minnu (8/5/2013)
Hi Team,I've having 120 tables in that 80+ tables have "emp_id" as a column_name, mistakenly i've inserted a record in to Employee table,
there are many triggers on that table, it will automatically inserts records into many tables with column_name "emp_id"
now i want to delete all the records in all tables with emp_id=136,
how to delete all the records in all tables contains emp_id=136
Please suggest..!
Unless you have some delete triggers you are going to have to create a delete for each of those tables. You will have to look at those triggers and follow the logic and delete whatever rows from whatever tables make sense. There is no automatic method to do this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2013 at 11:50 am
No direct way of doing this.
You could follow the previous comment and back track your triggers.
or
SELECT 'select '+sys.syscolumns.name +' FROM '+ sys.sysobjects.name +' where '
+sys.syscolumns.name +' = 136 'FROM sys.syscolumns
INNER JOIN sys.sysobjects
ON sys.syscolumns.id = sys.sysobjects.id
WHERE sys.syscolumns.name LIKE '%person%'
this wud return you a set of select statements, you could run each statement to check if such a row really exists in those tables. you can write a cursor over the query which I have provided.
August 5, 2013 at 12:00 pm
You could write a query to return a list of all tables that include the emp_id field name and build a SQL DELETE statement as a part of the query. Of course, there's no feasible way to determine in the query the order in which they would have to be executed if you have multiple parent-child relationships. In my opinion, it simply wouldn't be worth the effort to parse through all the foreign keys.
What I would suggest is to write a stored procedure to delete an employee so the next time this happens, the work will already be done. You'll need to start with your lowest-level children first, then proceed up one level until you get to the employees table, assuming that's the top-level parent.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply