Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS SQL truncate script needed

Posted on 2007-03-30
4
Medium Priority
?
696 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 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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

575 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