use master
Create Procedure SP_DROPALLTABLES With Recompile
as
------------------------------------------------------------
--- DELETES ALL TABLES IN THE CURRENT DATABASE !!        ---
--- EVENTUALLY AUTO-REMOVING THE FOREIGN KEYS  !!        ---
--- Script Written By Luis Dragotto, 22/01/99            ---
------------------------------------------------------------
declare @Id   int
declare @ForKeyName    varchar(255)
declare @ForTableName  varchar(255)
declare @ForKeyCount   int
declare @TableCount    int
declare @ContoStr      varchar(3)
declare @PrevTable     varchar(255)

SET NOCOUNT ON
if exists (select * from sysobjects where type='f')
BEGIN
 -- let's drop all the foreign keys (if exists at least one)
 declare cur_MyForeign cursor for select name,id from sysobjects where type='f'
 open cur_MyForeign
 select @ForKeyCount=0
 fetch next from cur_MyForeign into @ForKeyName, @Id
 While @@Fetch_Status=0
 BEGIN
  select @ForTableName=name from sysobjects where id=(select id from sysconstraints where constId=@Id)
  BEGIN
   exec ("alter table "+@ForTableName+" drop constraint "+@ForKeyName)
   select @ForKeyCount=@ForKeyCount+1
  END
  fetch next from cur_MyForeign into @ForKeyName, @Id
 END
 close cur_MyForeign
 deallocate cur_MyForeign
 select @ContoStr=convert(varchar,@ForKeyCount)
 exec ('print "Foreign Keys found and dropped :'+@ContoStr+'"')
END

-- Procediamo e droppiamo tutte le tabelle ...
if exists (select * from sysobjects where type='u')
BEGIN
 -- droppiamo tutte le tabelle
 declare cur_MyTable cursor for select name from sysobjects where type='u'
 open cur_MyTable
 select @TableCount=0
 fetch next from cur_MyTable into @ForTableName
 While @@Fetch_Status=0
 BEGIN
  exec ('drop table '+@ForTableName)
  select @TableCount=@TableCount+1
  fetch next from cur_MyTable into @ForTableName
 END
 close cur_MyTable
 deallocate cur_MyTable
 select @ContoStr=convert(varchar,@TableCount)
 exec ('print "Tables found and dropped :'+@ContoStr+'"')
END
SET NOCOUNT OFF
GO