Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Truncate All Tables Expand / Collapse
Author
Message
Posted Monday, November 17, 2008 2:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 30, 2011 1:00 AM
Points: 14, Visits: 17
/*I Do It As Follows:*/
DECLARE @ROWCOUNT INT
DECLARE @sSQL NVARCHAR(2000)
CREATE TABLE #DropTableNames(
TableName VARCHAR(30),
TableID INT IDENTITY(1,1) not null)
INSERT INTO #DropTableNames(TableName)
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Type='BASE TABLE'
SET @ROWCOUNT=@@ROWCOUNT
WHILE @ROWCOUNT>0
BEGIN
SELECT @sSQL='TRUNCATE TABLE '+TableName FROM #DropTableNames WHERE TableID=@ROWCOUNT
SET @ROWCOUNT=@ROWCOUNT-1
EXEC SP_EXECUTESQL @sSQL
END
/*My English is poor So ...Here Is No Marker*/
Post #603518
Posted Monday, November 17, 2008 5:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
If not references are present on the tables:
EXEC sp_MSForEachTable 'truncate TABLE ? ' will be enough to truncate all tables.
the sp_MSForEachTable replace the ? for the actual table Name.
Post #603577
Posted Monday, November 17, 2008 5:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
You can join the table with the names of tables also the statement.
Post #603580
Posted Monday, November 17, 2008 5:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:07 AM
Points: 329, Visits: 468
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-ii.aspx




Madhivanan

Failing to plan is Planning to fail
Post #603584
Posted Monday, November 17, 2008 3:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
Very good information on the link. Like the Blog stated there are many ways to aproach and problem..Thanks for the input.
Post #603978
Posted Thursday, October 28, 2010 7:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 28, 2010 6:57 AM
Points: 1, Visits: 0
i am vipul and i use this method to truncate all table from database. but constraint was not enabling by

Exec sp_MSforeachtable 'alter table ? check constraint all'
print 'all constraint enable'

MESSAGE WILL APPEAR THAT- 'all constraint enable'

BUT WHEN I CHECK THE DATABASE ALL CONSTRAINT DISABLE

SO WHAT I SHOULD DO ENABLE AGAIN ALL THE CONSTRAINT

VIPUL SACHAN
Post #1012275
Posted Thursday, October 28, 2010 7:28 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 3,081, Visits: 11,230
The script on the link below does a very good job of this without making any schema modifications, so it is a safer way to go.

Truncate All Tables
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341






Post #1012297
Posted Thursday, June 02, 2011 4:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 10:26 AM
Points: 200, Visits: 611
Just a thought, but if TRUNCATE then likely will reset SEEDS and there might be some cases where these might need to be kept? Maybe. Possibly. Especially if fed through to another system or report? Maybe.
Post #1118627
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse