Found on : http://www.eggheadcafe.com/software/aspnet/29145230/easiest-way-to-drop-all-t.aspx by ‘Damien’
It works :-)

I’m using it in our project with NHibernate, to recreate database schema.
There are some situation when NHibernate with Hbm2ddl.Auto = "create" won’t delete all old tables from db, and won’t create new schema.

declare boris cursor for
select
'alter table [' + USER_NAME(so.uid) + '].[' + OBJECT_NAME(sfk.fkeyid)
+ '] drop constraint [' + OBJECT_NAME(sfk.constid) + ']'
from
sysforeignkeys sfk
inner join
sysobjects so
on
sfk.fkeyid = so.id
where
OBJECTPROPERTY(so.ID,N'IsMSShipped') = 0 and
OBJECTPROPERTY(so.ID,N'IsTable') = 1

declare @sql varchar(8000)

open boris

fetch next from boris into @sql
while @@FETCH_STATUS = 0
begin
exec(@sql)

fetch next from boris into @sql
end

close boris
deallocate boris

declare boris cursor for
select
'drop table [' + USER_NAME(uid) + '].[' + OBJECT_NAME(ID) + ']'
from
sysobjects
where
OBJECTPROPERTY(ID,N'IsMSShipped') = 0 and
OBJECTPROPERTY(ID,N'IsTable') = 1

open boris

fetch next from boris into @sql
while @@FETCH_STATUS = 0
begin
exec(@sql)

fetch next from boris into @sql
end

close boris
deallocate boris