I am trying to search for the ? character in a string, but across all dbs on a server. Since the ? mark is a placeholder for the database name, how may I format the query so it knows I mean to search for ? and not replace it with the database name?
The WHERE clause at this time is:
WHERE DirectoryPath LIKE ''%?%''
This renders to:
WHERE DirectoryPath LIKE '%DATABASENAME%'
I need it to render as:
WHERE DirectoryPath LIKE '%?%'
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:
@replacechar is passed into sp_MSforeachdb as well as the second parameter so EXEC sp_MSforeachdb N'SELECT ''?!?''', N'!' works fine.
– Martin Smith
Oct 23 '13 at 23:03
? where you want the database name replaced to ! or some other character, and then it will leave your literal ? characters alone. Two challenges remain: (a) it's NCHAR(1), so you have to choose a single character that has no chance of being in the rest of the command naturally, and (b) you still have the potential issue of sp_MSforeachdb skipping databases.
– Aaron Bertrand♦
Oct 23 '13 at 23:53
