The first argument to the system stored procedure
= ] 'name'
Is the qualified or nonqualified name of a user-defined, schema-scoped object. Quotation marks are required only if a qualified object is specified. If a fully qualified name, including a database name, is provided, the database name must be the name of the current database. The object must be in the current database. name is
nvarchar(776), with no default.
In addition, the documentation for Delimited Identifiers (Database Engine) states:
Using Identifiers As Parameters in SQL Server
Many system stored procedures, functions, and DBCC statements take object names as parameters. Some of these parameters accept multipart object names, while others accept only single-part names. Whether a single-part or multipart name is expected determines how a parameter is parsed and used internally by SQL Server.
Single-part Parameter Names
If the parameter is a single-part identifier, the name can be specified in the following ways:
- Without quotation marks or delimiters
- Enclosed in single quotation marks
- Enclosed in double quotation marks
- Enclosed in brackets
Multipart Parameter Names
Multipart names are qualified names that include the database or schema name and also the object name. When a multipart name is used as a parameter, SQL Server requires that the complete string that makes up the multipart name be enclosed in a set of single quotation marks.
The first argument to
sp_helptext accepts both single-part (nonqualified) and multipart (qualified) object names.
If the T-SQL parser interprets the item after
sp_helptext as a single-part name (in accordance with the four bullet points above), the resulting name is passed as the (string type) argument value expected by the procedure.
When the parser sees it as a multipart name, the text is required to be surrounded with single quotation marks as stated.
The key feature of a multipart name is a
. separator (outside any delimiters).
These examples from the question are successfully interpreted as single-part names:
myproc - single-part (without quotation marks or delimiters - bullet #1)
[myproc] - single-part (in brackets - bullet #4)
'myproc' - single-part (in single quotation marks - bullet #2)
'dbo.myproc' - multipart with the required single quotation marks
[dbo.myproc] - single-part (in brackets - bullet #4)
The last two examples from the question are both parsed as multipart parameter names (due to the exposed
. separator). They produce an error because they lack the required enclosing single quotation marks:
dbo.myproc - multipart without the required single quotation marks
[dbo].[myproc] - multipart without the required single quotation marks
This extra example using double quotation marks is successful:
"dbo.myproc" - single-part (in double quotation marks - bullet point #3)
Note that it is successfully interpreted (for the procedure parameter value) as being a valid single-part name, but the procedure code is able to interpret the (multipart) string it receives flexibly (using
As a final point of interest, note that using double quotation marks here does not depend on the setting of