Solved

MS SQL truncate script needed

Posted on 2007-03-30
4
686 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 143

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

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

Learn about cloud computing and its benefits for small business owners.
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

856 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