Solved

MS SQL truncate script needed

Posted on 2007-03-30
4
679 Views
Last Modified: 2008-01-09
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?
0
Comment
Question by:dtotten800
  • 2
4 Comments
 
LVL 11

Expert Comment

by:Otana
ID: 18822773
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'

exec(@SQL)
0
 

Author Comment

by:dtotten800
ID: 18822819
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?
0
 
LVL 11

Expert Comment

by:Otana
ID: 18822830
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.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18824601
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...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now