CODEWITHSUNDEEP
sqlsql-deletetruncatesp-msforeachtable
PKirby
PKirby

Reputation: 879

Truncate all tables within a DB

I've executed the following script to delete/truncate all tables within a specific DB :

EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'
GO

Would the script above truncate all tables in all DBs or just the DB I'm running it in?

Upvotes: 0

Views: 105

Answers (1)

Midhun P
Midhun P

Reputation: 126

it will execute only in DB you are running it in. However for safety use USE yourDBName at the top of the query window

Try like this ,

USE yourDBName EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

Upvotes: 0

Related Questions