Have Geek Brain

How does one script truncate a specific table in each database?

I have Microsoft SQL Server 2008 with many databases and most of them have a log table. I would like to be able to schedule a script to run and truncate the log table in each of these databases (dynamically). I am assuming that I need to get the name of each user database and then truncate the Logs table in the databases that contain the logs table.

The statement I ended up using was:

EXEC sp_MSForEachDB 'Use [?]; IF  EXISTS (SELECT * FROM sys.tables WHERE name = "Logs" and type="U")TRUNCATE TABLE Logs'
0


a source to share


2 answers


A bit of a hack since the stored procedure is undocumented, but try this:



EXEC sp_MSForEachDB 'Use ?; TRUNCATE TABLE Logs'
+7


a source


In 2008, the easiest thing to do if it was not in production at the time is

Write a script that does this in dynamic sql



Alter Database <mydb> set recovery simple
go

Checkpoint
go

Alter Database <mydb> set recovery full
go
-1


a source






More articles:


All Articles