November 29, 2016 at 10:05 pm
Comments posted to this topic are about the item perform an activity on each database on the server
November 29, 2016 at 10:48 pm
This was removed by the editor as SPAM
November 30, 2016 at 5:52 am
Easy one, it is useful to know how to use sp_MSforeachdb. Thanks for this question Partha.
November 30, 2016 at 5:54 am
That was an easy one, but I applaud the question on something undocumented. I find the undocumented stuff interesting and enjoyable.
November 30, 2016 at 6:48 am
thanks for the question, trusty old gem used countless times.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 30, 2016 at 7:14 am
Do be careful using this procedure in a production environment. It will sometimes skip databases. http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx
Here is another great article on the topic. https://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/[/url]
_______________________________________________________________
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/
November 30, 2016 at 7:44 am
Sean Lange (11/30/2016)
Do be careful using this procedure in a production environment. It will sometimes skip databases. http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspxHere is another great article on the topic. https://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/[/url]
Yes it does. I don't use either version. I loop through them myself and do what I need to do to them.
November 30, 2016 at 10:12 am
Ed Wagner (11/30/2016)
Sean Lange (11/30/2016)
Do be careful using this procedure in a production environment. It will sometimes skip databases. http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspxHere is another great article on the topic. https://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/[/url]
Yes it does. I don't use either version. I loop through them myself and do what I need to do to them.
And if after all these years it is still undocumented then that does raise a caution flag for me.
----------------------------------------------------
November 30, 2016 at 11:43 am
Isn't using undocumented procedures simply asking for trouble ?
November 30, 2016 at 11:53 am
Not really. Many undocumented procedures work fine and are really useful.
November 30, 2016 at 12:16 pm
Partha Mandayam (11/30/2016)
Not really. Many undocumented procedures work fine and are really useful.
Depends on what you mean by working fine. You are stepping into the unknown and items like what Sean mentioned
Sean Lange (11/30/2016)
Do be careful using this procedure in a production environment. It will sometimes skip databases. http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx
Here is another great article on the topic. https://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/%5B/quote%5D
are not always readily apparent until you hit a corner case of some sort. Thus why I mentioned to use with caution. Always fully test with these, and even that may not be enough.
----------------------------------------------------
November 30, 2016 at 1:22 pm
i have experienced that SP skipping db's before. Couldn't figure out the pattern.
Switched to using scripts that invoke a query like 'select name from sys.databases
where name not in( 'tempdb', 'model')' on Master instead when i am moving something that hits everything to production.
December 1, 2016 at 6:48 am
What's with having something undocumented, so they can throw it out later?
December 1, 2016 at 7:02 am
Only Microsoft can answer that question:-)
December 1, 2016 at 7:52 am
It's so they don't have to officially support it. They don't have to guarantee results, MS Connect items aren't addressed and they don't have to maintain BOL. It saves them time, expense and headache.
That said, I do use undocumented stuff. I don't use the sp_msforeachdb because it's buggy. I use my own code to cycle through databases and can apply any WHERE clause and sort order I want. I've rewritten things and use my own version; I haven't tried the sp_msforeachdb because it's so simple to write the code myself and not have to use it that it's not worth the time.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply