February 5, 2016 at 4:48 pm
I am passing a string through my @myvariable to sproc ms_foreachdb, however i only want to execute this on few databases, how do i filter this when i pass a variable?
February 5, 2016 at 7:30 pm
See if this is what you are looking for(Suggested by
Vishal.Gajjar): http://www.sqlservercentral.com/Forums/Topic1199718-266-1.aspx
-Regards
February 5, 2016 at 10:14 pm
The ms_foreachdb uses a cursor to iterate through the databases on the instance. If you have to cursor through only a few databases, why not write your own cursor to define which ones to process? Then you won't have to worry about running through all of them or the occasional miss that happens every so often with ms_foreachdb.
February 6, 2016 at 2:07 am
Ed Wagner (2/5/2016)
The ms_foreachdb uses a cursor to iterate through the databases on the instance. If you have to cursor through only a few databases, why not write your own cursor to define which ones to process? Then you won't have to worry about running through all of them or the occasional miss that happens every so often with ms_foreachdb.
Make sure to declare that cursor either STATIC or FAST_FORWARD. Not using those options is exactly the reason why sp_msforeachdb occassionally fails.
February 6, 2016 at 7:24 pm
Hugo Kornelis (2/6/2016)
Ed Wagner (2/5/2016)
The ms_foreachdb uses a cursor to iterate through the databases on the instance. If you have to cursor through only a few databases, why not write your own cursor to define which ones to process? Then you won't have to worry about running through all of them or the occasional miss that happens every so often with ms_foreachdb.Make sure to declare that cursor either STATIC or FAST_FORWARD. Not using those options is exactly the reason why sp_msforeachdb occassionally fails.
Sure enough. When using a cursor is a necessity, it's fast forward and read only. At least it minimizes the impact of having to use one.
February 9, 2016 at 10:18 am
Most flexible is probably to put the dbs names/LIKE patterns into a temp table and process only names that have a match in that table:
IF OBJECT_ID('tempdb.dbo.#databases') IS NOT NULL
DROP TABLE #databases
CREATE TABLE #databases (
db_name_pattern varchar(128) NOT NULL
)
INSERT INTO #databases SELECT 'specific_db_to_process_1'
INSERT INTO #databases SELECT 'specific_db_to_process_2'
INSERT INTO #databases SELECT 'db_pattern_to_process%'
EXEC sp_MSforeachdb '
IF EXISTS(SELECT 1 FROM #databases d WHERE ''?'' LIKE d.db_name_pattern)
BEGIN
USE [?]
PRINT ''?''
--do your processing here
END /*IF*/
'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply