Export (0) Print
Expand All

Object Visibility and Qualification Rules

When you create an object, Microsoft SQL Server uses the following defaults for the parts of the name not specified.

Part not specified

Default

Server

Default is the local server.

Database

Default is the current database.

Schema_name

Default is the default schema of the current user in the current database.

For example, if a user is logged on to AdventureWorks2008R2 as the database owner user, dbo, either of the following two statements creates a table that is named AdventureWorks2008R2.dbo.TableX:

CREATE TABLE TableX (cola INT PRIMARY KEY, colb NCHAR(3));
GO

Or

CREATE TABLE AdventureWorks2008R2.dbo.TableX
      (cola INT PRIMARY KEY, colb NCHAR(3));
GO
NoteNote

You should specify the full table or view name to prevent possible confusion relating to the object you are working with.

Similarly, when you refer to an object, SQL Server uses the following defaults for the parts of the name not specified .

Part not specified

Default

Server

Default is the local server.

Database

Default is the current database.

Schema_name

Default is the default schema of the current user that is associated with the login ID of the current connection. If that user does not have a default schema, the schema will be the database owner (dbo) user.

For example, assume LoginX connects to a server that has two databases: DBY and DBZ. LoginX is associated with UserA in database DBY and with UserB in database DBZ.

LoginX executes a SELECT statement in the current database as follows:

USE DBY;
SELECT * FROM DBY..TableX;

Because LoginX is associated with UserA in DBY, SQL Server first looks for DBY.UserA.TableX. If there is no table with this name, SQL Server looks for a table DBY.dbo.TableX.

In the following example, LoginX executes a SELECT statement on a table that is not in the current database:

USE DBY;
GO
SELECT * FROM DBZ..TableY;
GO

Because LoginX is associated with UserB in database DBZ, SQL Server first looks for DBZ.UserB.TableY. If there is no table with this name, SQL Server then looks for a table DBZ.dbo.TableY.

NoteNote

SQL Server does not try to determine the owner of remote tables based on the current login. To make sure that distributed queries execute correctly, you should use fully qualified names.

The visibility for stored procedures that start with sp_ differs from the visibility for regular stored procedures. For more information, see CREATE PROCEDURE (Transact-SQL).

Community Additions

ADD
Show:
© 2014 Microsoft