TRUNCATING MULTIPLE TABLES

Question

Post reply

TRUNCATING MULTIPLE TABLES

david.foli7

SSC-Addicted

Points: 401
More actions
May 11, 2015 at 9:53 am

#318088

I got a request to truncate some tables on our testing servers.There are only 11 tables and i could go in and truncate them one after the other, but i need a script that i can use to truncate all the tables at a go. The tables also have no dependencies between them. A script to accomplish the same task if the tables had dependencies will also be appreciated.Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

What have you tried?

For scripting multiple tables to be trunctated it would be something like this:

TRUNCATE <Table1>;

TRUNCATE <Table2>;

TRUNCATE <Table3>;

TRUNCATE <Table4>;

--etc...

For scripting tables with dependencies do we know the dependencies ahead of time or do we need to figure that out on the fly? The former would be much less complicated.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Truncate Table T1

Truncate Table T2

...

Truncate Table T11

A truncate can only affect a single table so you have to write 11 truncate statements, and there cannot be a foreign key relationship to that table. If there is, the truncate will fail

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 3

here's how i get tables in the order of hierarchy; i could use this to build TRUNCATE/DELETE commands .

as i remember it SequenceOrder = 1 means i can truncate, and everything else must have a DELETE command, since there are FK 's holding it in place

CREATE TABLE #MyObjectHierarchy

(

HID int identity(1,1) not null primary key,

ObjectID int,

SchemaName varchar(255),

ObjectName varchar(255),

ObjectType varchar(255),

oTYPE int,

SequenceOrder int

)

--our list of objects in dependancy order

INSERT #MyObjectHierarchy (oTYPE,ObjectName,SchemaName,SequenceOrder)

EXEC sp_msdependencies @intrans = 1

UPDATE MyTarget

SET MyTarget.objectID = objz.object_id,

MyTarget.ObjectType = objz.type_desc

FROM #MyObjectHierarchy MyTarget

INNER JOIN sys.objects objz

ON MyTarget.ObjectName = objz.name

AND MyTarget.SchemaName = schema_name(objz.schema_id)

SELECT * FROM #MyObjectHierarchy WHERE ObjectType = 'USER_TABLE' ORDER BY HID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pietlinden SSC Guru Points: 63506 More actions · Answer 4

And then just to yank Jeff's chain, I'd throw in a cursor to loop over the tables and run the truncate statement on each one...

Yeah, just teasing, but I think a cursor might actually be a good idea here, because the tables have to be processed in order and one at a time.

Henry B. Stinson SSC Eights! Points: 949 More actions · Answer 5

Or, based on what I'm seeing in my database, the order of hierarchy is reversed, so it may be that the tables could be truncated in the order of

ORDER BY [SequenceOrder] DESC

or

ORDER BY [HID] DESC

but only after all FK constraints are dropped first -- and recreated after all the TRUNCATEs.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

pietlinden (5/11/2015)
And then just to yank Jeff's chain...

I know my response is a bit late but, remember... "Santa is watching". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)