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 '%?%'
up vote 6 down vote accepted

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:

  • Thank you so much for that. That is working. I would definitely like to have my own version of the sp_MSforeachdb at some point but this gets what I need done in a clever way. Thank you. – Josh Haley Oct 23 '13 at 19:28
  • 2
    @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
  • 1
    Ah, thanks @MartinSmith, I looked right past that. So in theory you could just change the ? 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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.