Click here to Skip to main content
15,888,313 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Everyone,
I have a need to truncate all tables in advwrks2008 /r2 in a single go using scripts or commands.
I have tried :
"
-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

--some of the tables have identity columns we may want to reseed them
EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)""

it executes only uptil halfway and shows many errors :
"Msg 1934, Level 16, State 1, Line 1
DELETE failed because the following SET options have incorrect settings:
DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Vendors cannot be deleted. They can only be marked as not active.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted."

Please help me out. Seeking help asap.

Regards
Abhishek
Posted
Comments
Corporal Agarn 3-Feb-15 6:49am    
Do you have any spaces or special characters in your table names? Look at the function QUOTENAME

1 solution

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900