A more reliable and more flexible sp_MSforeachdb

Posted by AaronBertrand on SQL Blog See other posts from SQL Blog or by AaronBertrand
Published on Wed, 29 Dec 2010 16:02:00 GMT Indexed on 2010/12/29 16:58 UTC
Read the original article Hit count: 165

Filed under:

Maintenance

|

sp_MSforeachdb

|

system objects

I've complained about sp_MSforeachdb before. In part of my "Bad Habits to Kick" series in 2009-10, I described how I worked around its sporadic inability to actually process all of the databases on an instance: http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior.aspx I lumped this in a "Bad Habit" category of relying on undocumented behavior, since - while the procedure does have rampant usage - it is, in fact, both undocumented and unsupported....(read more)

© SQL Blog or respective owner

Related posts about Maintenance

htaccess code for maintenance page redirect

as seen on Pro Webmasters - Search for 'Pro Webmasters'
I set up a maintenance page that I could enable through an htaccess file. The html file is located in a folder called "maintenance". The html file has some images in it. However, visitors to the page see no images, even though I added a line to allow them. If I try to visit an image in the browser… >>> More
Tools to (privately) annotate/markup a website for maintenance

as seen on Super User - Search for 'Super User'
I've been tasked with updating a website. Rather than proofreading and updating each page (one at a time), I want to make a single pass over the entire website, marking graphics/images/videos that need to be rewritten, removed, or updated. I thought about taking screenshots, marking those up, and… >>> More
Activate site maintenance via RewriteCond -f check?

as seen on Stack Overflow - Search for 'Stack Overflow'
I thought about hitting a file via -f (i think it is) in a mod_rewrite RewriteCond, if the file exists, rewrite to maintenance page. I don't think the performance penalty should be too big, but maybe people had other experiences or objections on this idea? >>> More
Exam 70-448 - TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance

as seen on ASP.net Weblogs - Search for 'ASP.net Weblogs'
The another exam I passed was 70-448 - TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance. This exam covers Business Intelligence (BI) solutions development and maintenance on SQL Server 2008 platform. It was not easy exam, but if you study then you can do it. To get… >>> More
Oracle presents an upgrade tool for the E-Business Suite: Oracle Maintenance Wizard

as seen on Oracle Blogs - Search for 'Oracle Blogs'
Vikki Pickard, Product and Development Manager for the Oracle Maintenance Wizard introduces a very useful tool that guides customers through E-Business Suite upgrades and the code line maintenance process. >>> More

Related posts about sp_MSforeachdb

A more reliable and more flexible sp_MSforeachdb

as seen on SQL Blog - Search for 'SQL Blog'
I've complained about sp_MSforeachdb before. In part of my "Bad Habits to Kick" series in 2009-10, I described how I worked around its sporadic inability to actually process all of the databases on an instance: http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior… >>> More
Making a more reliable and flexible sp_MSforeachdb

as seen on SQL Server Central - Search for 'SQL Server Central'
While the system procedure sp_MSforeachdb is neither documented nor officially supported, most SQL Server professionals have used it at one time or another. This is typically for ad hoc maintenance tasks, but many people (myself included) have used this type of looping activity in permanent routines… >>> More
Connect Digest : 2012-07-06

as seen on SQL Blog - Search for 'SQL Blog'
I've filed a few Connect items recently that I think are important. In #752210 , I complain that the documentation for DDL triggers suggests that they can prevent certain DDL from being run, which is not the case at all. http://connect.microsoft.com/SQLServer/feedback/details/752210/doc-ddl-trigger-topic-suggests-that-rollbacks-run-before-action… >>> More
Connect Digest : 2012-07-06

as seen on SQL Blog - Search for 'SQL Blog'
I've filed a few Connect items recently that I think are important. In #752210 , I complain that the documentation for DDL triggers suggests that they can prevent certain DDL from being run, which is not the case at all. http://connect.microsoft.com/SQLServer/feedback/details/752210/doc-ddl-trigger-topic-suggests-that-rollbacks-run-before-action… >>> More
The Exceptional EXCEPT clause

as seen on SQL Blogcasts - Search for 'SQL Blogcasts'
Ok, I exaggerate, but it can be useful… I came across some ‘poorly-written’ stored procedures on a SQL server recently, that were using sp_xml_preparedocument. Unfortunately these procs were not properly removing the memory allocated to XML structures – ie they were not subsequently calling… >>> More