I need SQL statement to delete all data from all tables in a SQL db

Hi,

I need SQL statement to delete all data from all tables in a SQL db.
Database name is TEST and it got 800 tables.
How to delete all data in all tables??

Thanks,
HP
HPvHAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
shorter:
exec sp_msforeachtable ' DELETE FROM ? '

Open in new window

0
 
Alpha AuCommented:
Use carefully
use TEST
 
create table #tblList(
col1 varchar(1000),
col2 varchar(1000),
col3 varchar(1000),
col4 varchar(1000),
col5 varchar(1000)
)
 
insert into #tblList
	exec sp_tables
 
declare @sqlcmd nvarchar(4000)
 
declare sel_cur cursor for 
select 'delete from ' + col3 from #tblList where col4 = 'TABLE'
open sel_cur
fetch next from sel_cur into @sqlcmd
while @@fetch_status = 0
begin			
--select @sqlcmd	
sp_executesql @sqlcmd
 
fetch next from sel_cur into @sqlcmd
end
close sel_cur
deallocate sel_cur

Open in new window

0
 
bradleys40Commented:
Run this in query analyser
Select 'truncate table ' + Name from sysobjects where xtype = 'U'
0
All Courses

From novice to tech pro — start learning today.