MS SQL truncate script needed

I have a MS SQL 2005 database with a 100 tables

I want to remove all rows entries in each of the tables

Do someone have a Microsoft SQL SCRIPT that i can run for this?
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
another way:

exec sp_MSForEachtable ' TRUNCATE TABLE ? '

however, if you have foreign keys, you cannot use truncate, you must use delete instead, and need to repeat the procedure until all tables are deleted

note: be 200% sure that you are on the correct database !!!!!!!!!!!!!!!!!!!!

note: you should write a script manually that deletes / truncated all the tables.
sql enterprise manager has a menu to script out all tables, including drop statements. that will be much shorter to do, and be clean

tip: if you need to do this regulary, you should rather have a template database, backup that template, and restore over you database...
Try this, it will put all user-defined tables in a script:

declare @SQL varchar(8000)

set @SQL = ''

select @SQL = @SQL + ' truncate table ' + name from sysobjects where type = 'U' and name <> 'dtproperties'

dtotten800Author Commented:
I don't quite understand,

I would like to delete all rows from all tables.

Could you please give me step by step instructions on how to do so?
Paste the code I gave you in Query Analyzer, and run it. It will create a variable which will look like this: "truncate table Table1 truncate table Table2 truncate table Table3" etc...

Then, it will execute the commands in the variable, so it will run all the truncate statements, and your tables should be empty.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.