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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
shorter:
exec sp_msforeachtable ' DELETE FROM ? '

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bradleys40Commented:
Run this in query analyser
Select 'truncate table ' + Name from sysobjects where xtype = 'U'
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.