two improvement to the "drop all table with T-SQL" solution

two improvement for above solution. first is:avoid dead lock and use CURSOR variant second is:use sp_MSForEachTable (in fact,you can execute " EXEC sp_MSForEachTable 'drop table ?' " many times directly to achive this goal) --delete all tables under current DB --written by shenjian @ 2006/03/18 --if "foreign key constraint error" occur,just ignore it. declare @tblName varchar(2000), @count int , @ExecStr varchar(2000), @localcounter int, @cursor_tblName CURSOR set @count=0 set @localcounter=1 --only need a <>0 value to start the loop,no care what it is --if no talbe to be dealt or no table to be dealt success in one round,then quit while @localcounter<>0 --to deal the foreign key constraint begin set @localcounter=0 --cursor should be cycled for next time set @cursor_tblName = CURSOR FOR select name from sys.tables order by name open @cursor_tblName fetch @cursor_tblName into @tblName while @@fetch_status =0 begin select @ExecStr='drop table "'+@tblName+'"' --print('SQL string:'+@ExecStr) EXEC (@ExecStr) if(@@error=0) begin set @localcounter=@localcounter+1 end fetch @cursor_tblName into @tblName end set @count=@count+@localcounter close @cursor_tblName deallocate @cursor_tblName end print('.....drop all table success....') print('drop '+convert(varchar(5),@count)+' tables') go -----another improvement. --delete all tables under current DB --written by shenjian @ 2006/03/18 --if "foreign key constraint error" occur,just ignore it. declare @totalcount int ,--total count @currentCount int,-- current count @countAfterCycle int,--table count after one round of "drop" @tblDelNum int --the table numbers deleted in one round --save tables count select @totalcount=count(*) from sys.tables select @currentCount=@totalcount select @tblDelNum=1 --only need @tblDelNum >0 value to start the loop --if no talbe to be dealt or no table to be dealt success in one round,then quit while (@tblDelNum>0 and @totalcount<>0)--loop for dealing the foreign key constraint begin EXEC sp_MSForEachTable 'drop table ?' select @CountAfterCycle=count(*) from sys.tables set @tblDelNum=@CurrentCount-@CountAfterCycle set @CurrentCount=@CountAfterCycle end select @tblDelNum=@totalcount-count(*) from sys.tables if(@tblDelNum<>@totalcount) print('...some talbe not deleted...') else print('...drop all table success...') print( '(total:'+convert(varchar(5),@totalcount) +',deleted:'+convert(varchar(5),@tblDelNum)+')') go NB:Two article about "drop all tables with T-SQL" were posted in my spaces in msn, now it's moved to be here.