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

May 26, 2010 8:46 AM

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!

posted by DakotaPaul to Computers & Internet (11 answers total)

Do the foreign keys have "on delete cascade" set, or can they be modified?
posted by Electric Dragon at 9:00 AM on May 26, 2010

Response by poster: No (see paragraph above the code), and I can't modify the tables, unfortunately.
posted by DakotaPaul at 9:25 AM on May 26, 2010

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.
posted by cmm at 9:45 AM on May 26, 2010

Response by poster: 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.
posted by DakotaPaul at 9:56 AM on May 26, 2010

Best 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.
posted by hoppytoad at 10:00 AM on May 26, 2010 [1 favorite]

Best answer: 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
posted by cmm at 10:27 AM on May 26, 2010

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.
posted by cmm at 10:29 AM on May 26, 2010

Best answer: 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).
posted by orthogonality at 11:04 AM on May 26, 2010

Response by poster: I played with TABLE_CONSTRAINTS that hoppytoad suggested, 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!
posted by DakotaPaul at 12:48 PM on May 26, 2010

Response by poster: Oops, those <>'s should have looked like double arrows.
posted by DakotaPaul at 12:55 PM on May 26, 2010

Response by poster: Here's what I ended up doing: Error 547 is what I was getting when trying to delete the first time.

declare @table varchar(128), @restart bit, @err int, @stmt nvarchar(256)
declare tblcurs scroll cursor for
   select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'di_no'
open tblcurs

fetch next from tblcurs into @table

while 1=1
begin
   set @restart = 0

   while @@fetch_status = 0
   begin
      set @stmt = N'delete from ' + @table + ' where di_no = 1'
      exec @err = sp_executesql @stmt

      if @err = 547
         set @restart = 1

      fetch next from tblcurs into @table

   end

   if @restart = 1
      fetch first from curs into @table
   else
      break

end
close tblcurs
deallocate tblcurs

posted by DakotaPaul at 1:54 PM on May 26, 2010