drop all tables in database by T-SQL in SQLServer

firstly I searched this topic in Google, but unfortunately, I cannot find any valueable article to

resolve it. someone think give an " drop database ; create database" idea.but always it doesn't match for our true intention. the reason is that this solution will delete all the related objects and even serious, the security (previleges grant) are also deleted. It make the table restore difficult.

in fact,it is really easy to implement it by an T-SQL.

following is my solution. (enviorment windows2003+SQL SERVER2005).

 

--it's my first time to write T-SQL function,share it for commemoration:-)

--delete all tables under current DB
--written by shenjian @ 2006/03/17
--if "foreign key constraint error" occur,just ignore it.
declare
   @tblName varchar(2000),
   @count int ,
   @ExecStr varchar(2000),
   @localcounter int

set @count=0
set @localcounter=1 --only need a <>0 value to start the loop,no care what it is
while @localcounter<>0 --to deal the foreign key constraint
begin 
    set @localcounter=0
    --cursor should be cycled for next time
    declare curName cursor for
    select name from sys.tables order by name
 open curName

 fetch curName into @tblName   
 while @@fetch_status =0
 begin
  set @localcounter=@localcounter+1
  select @ExecStr='drop table " '+@tblName+'"'
  --print('SQL string:'+@ExecStr)
  EXEC (@ExecStr)
        if(@@error<>0)
  begin         
   print(@@error)
  end
  fetch curName into @tblName
 end
 set @count=@count+@localcounter
    close curName
    deallocate curName
end
print('.....drop all table success....')
print('drop '+convert(varchar(5),@count)+' tables')
go