Export (0) Print
Expand All

Using Identifiers as Object Names

SQL Server 2000

The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. They appear in the following format:

[ [ [ server. ] [ database ] . ] [ owner_name ] . ] object_name

The server, database, and owner names are known as the qualifiers of the object name. When referring to an object, it is not necessary to specify the server, database, and owner. The qualifiers can be omitted by marking their positions with a period. The valid forms of object names are:

server.database.owner_name.object_name
server.database..object_name
server..owner_name.object_name
server...object_name

database.owner_name.object_name
database..object_name

owner_name.object_name

object_name

An object name that specifies all four parts is known as a fully qualified name. Each object created in Microsoft® SQL Server™ must have a unique, fully qualified name. For example, there can be two tables named xyz in the same database if they have different owners.

Column names must be unique within a table or view. Assume that both a table and a view in the customer database have the same column named telephone. To refer to the telephone column in the employees table, specify customer..employees.telephone. To refer to the telephone column in the mktg_view view (marketing department view), specify customer..mktg_view.telephone.

Most object references use three-part names and default to the local server. Four-part names are generally used for distributed queries or remote stored procedure calls and use this format.

linkedserver.catalog.schema.object_name

The table shows the part names and their descriptions.

Part name Description
linkedserver Name of the linked server that contains the object referenced by the distributed query.
Catalog Name of the catalog that contains the object referenced by the distributed query.
Schema Name of the schema that contains the object referenced by the distributed query.
object_name Object name or table name.

For distributed queries, the server part of a four-part name refers to a linked server. A linked server is a server name defined with sp_addlinkedserver. The linked server identifies an OLE DB provider and an OLE DB data source that can return a record set that SQL Server can use as part of a Transact-SQL statement.

See the documentation for the OLE DB provider specified for the linked server to determine what components in the OLE DB data source are used for the catalog and schema parts of the name. If the linked server is running an instance of SQL Server, the catalog name is the database containing the object, and the schema is the owner of the object. For more information about four-part names and distributed queries, see Distributed Queries.

For remote procedure calls, the server part of a four-part name refers to a remote server. A remote server, which is specified with sp_addserver, is an instance of SQL Server accessed through the local server. Execute stored procedures on the remote server using this format for the procedure name:

server.database.owner_name.procedure

All four parts of the name are required when using a remote stored procedure. For more information about remote servers, see Configuring Remote Servers.

See Also

FROM

Transact-SQL Syntax Conventions

Show:
© 2014 Microsoft