Truncating and deleting all tables in a database

set nocount on

declare @owner sysname
declare @tn sysname
declare @sql nvarchar(4000)
declare @usertable smallint

create table #tbltemp (oType smallint, oObjName sysname, oOwner sysname, oSequence smallint)
create table #DepList (objid int null, objtype smallint null)

/* just user tables*/set @usertable = 3

insert #DepList select id, @usertable from sysobjects where objectproperty(id, 'IsTable') = 1 and objectproperty(id, 'IsMSShipped') = 0

/* flags = 131080 comes from *//* 3  - user table + *//*  0x20000 (131072) = descending return order*/
/* if you want to include transaction, add the *//* corresponding begin, commit or rollback transaction*//* and change @intrans to 1 */insert #tbltemp EXEC sp_MSdependencies @objname = null, 
@objtype = null, 
@flags = 131080, 
@objlist = '#DepList',
@intrans = 0

drop table #DepList

declare mycur cursor
forward_only
for
select oOwner, oObjName from #tbltemp

open mycur

fetch next from mycur into @owner, @tn

while @@fetch_status = 0
begin

set @owner = rtrim(cast(@owner as char))
        set @tn = @owner + '.' + @tn

if exists (select * from sysreferences where rkeyid = object_id(@tn))
                /*table referenced by a FOREIGN KEY constraint */  
set @sql = 'delete ' + @tn
else
set @sql = 'truncate table ' + @tn

execute sp_executesql @sql

print @sql

fetch next from mycur into @owner, @tn
end

close mycur
deallocate mycur

drop table #tbltemp

set nocount off