Cycle Through Multiple db's For EXISTence of a Table then DELETE

Question

Cycle Through Multiple db's For EXISTence of a Table then DELETE

PrettyMegaDBA

SSC Veteran

Points: 274
More actions
May 4, 2016 at 8:59 am

#316275

Hi,
I would DELETE records older than 7 days of getedate() in all databases on an instance where the table_name exists.
So far I have this where I am deleting in batches but I am unsure how to handle dynamic db names. I thought about MSForReachDB, but was unsure if this is the right approach.
---Add database cycle
DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;
WHILE (@Deleted_Rows > 0)
BEGIN
BEGIN TRANSACTION
-- Delete some small number of rows at a time
DELETE TOP (10000) dbo.table_name
WHERE EffDate < dateadd(DAY,-7,GETDATE())
COMMIT TRANSACTION
--CHECKPOINT -- for simple recovery model
SET @Deleted_Rows = @@ROWCOUNT;
END
Any theories, help and assistance is appreciated.
C~

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

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

Y.B. SSChampion Points: 11649 More actions · Answer 1

If you are asking if it's a good idea to use 'sp_msforeachdb' you just have to keep in mind that it's an undocumented procedure. So you never know how it will be supported in the future. That being said I've never had any issues using it when I needed to.

As for it's usage, here is a simple example. Notice the question mark. The procedure will substitute any of those for the database names.

EXEC sp_msforeachdb 'USE [?] SELECT * FROM INFORMATION_SCHEMA.TABLES'

Cheers,

SELECT quote FROM brain WHERE original = 1
0 rows returned

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Be careful using sp_msforeachdb. It can sometimes skip databases. Even more shocking is many of the major players don't really know why. http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

PrettyMegaDBA SSC Veteran Points: 274 More actions · Answer 3

Thank you both for replying.

Sean, what would an alternative be?

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

PrettyMegaDBA (5/4/2016)
Thank you both for replying.
Sean, what would an alternative be?

well...you could start by reading the article I posted. It has a link to Aaron's version which works better. Or you can use the native undocumented version. I merely want to point out that it can sometimes skip databases.