what does EXEC master.. Do?

Question

I have seen this like:

EXEC master.dbo.xp_cmdshell

What does master refer to?

Update
And why sometimes is followed by two points:

 master..

generally we would use master.dbo. Am I correct, so why some people write master..?

Solution 1

master is one of the default SQL Server system databases. You can tell because what you posted:

EXEC master.dbo.xp_cmdshell

...uses the three name notation. "master" is in the database position, "dbo" is the schema, and "xp_cmdshell" is the function/stored procedure in this case. You use this notation for also referring to tables and views, in different contexts.

This:

EXEC master..xp_cmdshell

...just omits the schema, but isn't a good idea if there are more than one schema being used in a database.

Solution 2

In Transact SQL, the fully qualified path to any object is:

server_name.db_name.owner.object parm1, ...

• The dots separate the four components
• the first three components have defaults:
  • the current server
  • the current database
  • dbo (database owner) (which should be the owner of the shared tables)

master is the system database that defines the server.

For MS, since the system stored procs are still located in master, the following is completely redundant:

EXEC master.dbo.stored_proc_name parm1, ...

EXEC stored_proc_name parm1, ...

EXEC

stored_proc_name parm1, ...

There is no difference between master.. and master.dbo.. They are both unnecessary when addressing system stored procs.

Solution 3

It refer to a stored procedure that has been created (by default) in the master table.

The name master refer to the database that contains the different schema of your server instance.

Comments

[OMG Ponies]

@darkcminor: It's safer when using against a default database (vs user created databases), but the three name notation is a very good practice.

[Pierre-Alain Vigeant]

three-part and four-part column references are deprecated in Sql Server 2008, R2 and Denali. See the feature #3 at msdn.microsoft.com/en-us/library/ms143729.aspx

[OMG Ponies]

@Pierre-Alain Vigeant: Thx, didn't know that -- stuck with 2005. Guess that means using synonyms.