Updates thanks to Martin. If you view the text for the procedure:
EXEC sys.sp_helptext N'sp_MSforeachdb';
You will see that it passes the @replacechar
as NCHAR(1)
(default N'?'
) into sys.sp_MSforeach_worker
. Peeling back another layer of the onion, you will see really ugly code in that procedure that loops through every character in the command and rebuilds it. I didn't study every single line but I could not find any evidence of an ability to escape or preserve the actual question mark conditionally, unless - as Martin points out - you override it with a different character that can't naturally appear in the command. So perhaps something like:
EXEC sp_MSforeachdb N'SELECT ''Ƥ'', other_columns FROM [?].dbo.whatever
WHERE DirectoryPath LIKE ''%?%'';', N'Ƥ';
-- Ƥ is NCHAR(420), chosen randomly
Since it is really looking for the literal ?
character, a possibly easier way would be this:
EXEC sys.sp_MSforeachdb N'PRINT ''?'' + ''-'' + CONVERT(VARCHAR(32), 0x3F)';
So extrapolating that, we can construct this ugly variation:
EXEC sys.sp_MSforeachdb N'SELECT ''?'', other_columns FROM [?].dbo.whatever
WHERE DirectoryPath LIKE ''%'' + CONVERT(VARCHAR(32), 0x3F) + ''%'';';
That all said, you could write your own version of sp_MSforeachdb
that uses a different default value for @replacechar
, so you don't have to override it manually. You probably should be writing your own version instead of relying on the built-in procedure anyway, as I point out in these posts - it is unsupported, undocumented, and actually has a very high frequency bug where it skips databases due to the cursor options used: