What does ''?'' mean in SQL Server?

Question

I've written this code and someone helped me out but I can't figure out what ''?'' means to the DB engine. It would really help me understand what I've written here.

 USE master
 EXEC sp_MSforeachdb 'use ? select ''?'', st.NAME, sc.NAME,         
 sc.system_type_id, db_name()
 FROM sys.tables st
 INNER JOIN sys.columns sc ON st.object_id = sc.object_id
 WHERE (st.name LIKE ''rawp%'' or st.name like ''main'')'

I understand I'm searching all db in the instance for the name of the columns and their related tables but I need to understand how ''?'' fits into the Query. Thanks!

Answer 1

From http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx

Notice that [?] is used as a placeholder for the heretofore unspecified database name

So the ? will be replaced with the database name.

Note that it is probably better to write it as

EXEC sp_MSforeachdb 'use [?] select ''?'', st.NAME, sc.NAME, 

to support databases with "funny" names, like [Hello[] World'

Answer 2

The ? is a placehold for all database names when you use sp_MSforeachdb. So when it loops through all databases, it replaces the ? with the actual database name in the specified ad hoc query you are calling the stored procedure with.

Note: it is not recommended to use this stored procedure. Please see the following blog post on reasons why not to use sp_MSforeachdb.

Answer 3

This usually implies a prepared statement, Parameters are filled in later. (see http://en.wikipedia.org/wiki/Prepared_statements#Parameterized_statements). But here it is a placeholder for the database name

Answer 4

You may want to check out this question, which has the answer.

What does a question mark represent in SQL queries?

Answer 5

? it is usually a placeholder for parameters that will be filled in later. See https://en.wikipedia.org/wiki/Prepared_statement#Parameterized_statements for more details.

Answer 6

sp_msforeachdb is kind of... special. And I mean partly in the "riding the short bus" sense of the word (and even that's not entirely fair here; my main complaint is that it's undocumented), but mostly I just mean that it's unique. This syntax is only used with sp_msforeachdb and sp_msforeachtable, and nowhere else in Sql Server itself.

What you should think of, though, is if you've ever written code that used an OLE or ODBC provider to do parameterized queries. Those tools use the ? character as a parameter placeholder. Something similar is going on here. The ? character is a parameter placeholder, where the value of the parameter will later be set to the name of each DB in your server, including master, tempdb, model, and msdb.

Try running this code to get a sense of how it works:

EXEC sp_MSforeachdb 'print ''?'' '

It's tempting to use this procedure for things like maintenance, reporting, and alerting scripts, but it's something you probably want to do only sparingly. Best not to rely on undocumented behavior. The sqlblog link posted first in another answer is the standard thinking on the subject.

Basically, there are likely no specific plans to retire or break this procedure (or other sp_* procedures), but there is also pretty much zero investment in moving these procedures forward from version to version. For example, the linked article indicates that sp_msforeachdb relies on the old dbo.sysdatabases rather than the more-correct sys.databases. I wouldn't expect Microsoft to target sp_msforeachdb directly, but if they ever decide to remove dbo.sysdatabases my expectation would be that their testing will discover that sp_msforeachdb is broken, at which time they'll also just remove sp_msforeachdb rather than fix it.