Why do system stored procs fail with the owner included?

Question

In SQL Server, the following works:

sp_help MyProc
sp_depends MyTable

But if you include the owner, it fails with a syntax error:

sp_help dbo.MyProc
sp_depends dbo.MyTable

It makes you put the param in quotes for it to work. Yet, I could do the following no problem:

exec dbo.MyProc

Why the inconsistency? Is there a reason?

Answer 1

SQL Server allows you to omit single quotes for single-word string parameters:

This will output test:

CREATE PROCEDURE prc_test (@objname NVARCHAR(255))
AS
BEGIN
        SELECT  @objname
END

prc_test test

, however this will fail:

prc_Test Cannot_insert_a_dot.here

Here, test is a short form of 'test' (note the single quotes).

SP_HELP is a stored procedure that expects a VARCHAR parameter, while EXECUTE expects an object identifier (of which a database name is a valid part)

exec dbo.MyProc

Always enclose your string constants into single quotes, and it can be a good idea to enclose the object names into brackets:

EXEC [dbo].[myproc]

Answer 2

Completing the Quassnoi answer.

SP_Help is a SP in the master DB that takes a NVarchar argument for the name of the SP. If you use it consistently, you should call

sp_help 'MyProc'

And, with this same convention

sp_help 'dbo.MyProc'

works fine.

The thing is that, when you call sp_help MyProc SQL Server adds the single quotes for you, but this does not work when using a dot in the name of the SP.