Truncate All Tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jorge Novo » Truncate All Tables

18 posts, Page 2 of 2

««1 2

Truncate All Tables

Rate Topic

Display Mode

Topic Options

Author

Message

luckyHailong

Posted Monday, November 17, 2008 2:38 AM

Grasshopper

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

Jorge Novo-@ETLDEVDBA

Posted Monday, November 17, 2008 5:23 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:55 AM
Points: 137, Visits: 250

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

Jorge Novo-@ETLDEVDBA

Posted Monday, November 17, 2008 5:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:55 AM
Points: 137, Visits: 250

You can join the table with the names of tables also the statement.

Post #603580

Madhivanan-208264

Posted Monday, November 17, 2008 5:36 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 5:17 AM
Points: 329, Visits: 461

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

Jorge Novo-@ETLDEVDBA

Posted Monday, November 17, 2008 3:01 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:55 AM
Points: 137, Visits: 250

Very good information on the link. Like the Blog stated there are many ways to aproach and problem..Thanks for the input.

Post #603978

vipulsachan.kiet 1412

Posted Thursday, October 28, 2010 7:01 AM

Forum 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

Michael Valentine Jones

Posted Thursday, October 28, 2010 7:28 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:01 PM
Points: 2,944, Visits: 10,504

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

Michael.Beeby

Posted Thursday, June 02, 2011 4:47 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 4:14 AM
Points: 198, Visits: 588

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 »

18 posts, Page 2 of 2

««1 2

Permissions