Export (0) Print
Expand All

Object Visibility and Qualification Rules

When you create an object, Microsoft SQL Server 2005 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 username in the specified database that is associated with the login ID of the current connection.

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

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

Or

CREATE TABLE AdventureWorks.dbo.TableX
      (cola INT PRIMARY KEY, colb NCHAR(3));
GO
ms175821.note(en-US,SQL.90).gifNote:
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 username in the specified database that is associated with the login ID of the current connection. If that user owns no object that has the specified name, SQL Server looks for an object with the specified name owned by 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.

ms175821.note(en-US,SQL.90).gifNote:
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