Find solution
How to convert below SQL Expression into derived column in SSIS?

Delete records from every table with a certain column in SQL Server 2005?

  • SQL Server 2005: How do I create a script to delete specific rows from every table that has a certain column, deleting from the tables with foreign keys first? Nearly every table in the database has the column some_id, and I want to delete the records in those tables where some_id = 1. There are no cascading deletes or triggers to help me out. Here's what I have so far: declare @table varchar(128) declare tblcurs cursor for    select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'some_id' open tblcurs fetch next from tblcurs into @table while @@fetch_status = 0 begin    exec ('delete from ' + @table + ' where some_id = 1')    fetch next from tblcurs into @table end close tblcurs deallocate tblcurs How can I modify this so tables with foreign keys are deleted first? I.e., some_detail_tbl is deleted before some_header_tbl. (And some_id is not the only column the key is based on.) Also, if there's a better/more elegant/more efficient method than what I have, I'd love to hear it. Thanks!

  • Answer:

    Maybe you could join the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view which has a CONSTRAINT_TYPE column and then sort on it to get tables with foreign keys to show up first in the cursor.

DakotaPaul at Ask.Metafilter.Com Visit the source

Was this solution helpful to you?

Other answers

Do the foreign keys have "http://msdn.microsoft.com/en-us/library/ms186973%28SQL.90%29.aspx" set, or can they be modified?

Electric Dragon

No (see paragraph above the code), and I can't modify the tables, unfortunately.

DakotaPaul

How does the exec handle errors? Does it just set @@error? If so, then skip that table and move on to the next. The first run over the list will do all the tables with no FKs in the way. Then run over the list again and more will work. Then run it over again and again until all DELETE FROMs update no records.

cmm

I did try replacing the exec with a print, and ran the resulting SQL manually. From there I could see which deletes failed, and modified the script to do something like: if @table = 'some_table' begin    delete from some_other_table where some_id = 1    delete from some_table where some_id = 1 end And do that over and over until I there are no FK constraint errors left, but I was hoping to do that all in one script and run it one time.

DakotaPaul

Just do a while in the SQL, though so it does the work. Again I don't know if EXEC sets @@rowcount/@@error or if there is an issue if it will kill the batch. Something like declare @deleteCount int declare @doneYet int set @deleteCount = 0 set @doneYet = 0 while @doneYet 1 begin if @deleteCount = 0 set @doneYet = 1 else set @deleteCount = 0 end>

cmm

I lost something there. Inside the while, do your stuff you had above pulling up your tables and doing your execs and add @@rowcount to @deleteCount with each one.

cmm

Two options: do what hoppytoad said, and use a join to information_schema.table_constraints to order your list of tables to delete from; OR, just resume on error and keep looping as long as select count(*) from table where id= x returns greater than zero: eventually you'll delete the root table entry, then you'll repat the list of tables and delete top-level children, then.... (note, fails for circular references).

orthogonality

I played with TABLE_CONSTRAINTS that http://ask.metafilter.com/154985/Delete-records-from-every-table-with-a-certain-column-in-SQL-Server-2005#2222137suggested, getting a list of every table with CONSTRAINT_TYPE of FOREIGN_KEY, but it didn't quite work because of relationships like some_tbl some_other_tbl yet_another_tbl>> I can't figure out how to delete yet_another_tbl before some_tbl. Will try the looping methods suggested. Thanks for the help so far!

DakotaPaul

Oops, those 's should have looked like double arrows.>

DakotaPaul

Load more

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.