Technical Article

Truncating and deleting all tables in a database

,

Sometimes, during test stage, you may need to truncate or delete all your tables in a database, without droping constraints, truncating and recreating constraints (DRI). You can use the TRUNCATE TABLE statement to removes all rows from a table without logging the individual row deletes, but tables referenced by a FOREIGN KEY constraint can not be truncated. For them, you can use the DELETE statement, but you have to do it in the rigth order so you do not delete first the rows from a parent table and get an error because the DRI. This is what this script does.

This script does not check permissions, it supposes that you have sufficient to truncate or delete.

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

Rate

4.67 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

4.67 (3)

You rated this post out of 5. Change rating