Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS SQL truncate script needed

Posted on 2007-03-30
4
Medium Priority
?
693 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

604 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