Using Identifiers As Object Names
The complete name of an object is made up of four identifiers: the server name, database name, schema name, and object name. They appear in the following format:
[ [ [ server. ] [ database ] . ] [ schema_name ] . ] object_name
The server, database, and owner names are known as the qualifiers of the object name. When you refer to an object, you do not have 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 include the following:
- server.database.schema_name.object_name
- server.database..object_name
- server..schema_name.object_name
- server...object_name
- database.schema_name.object_name
- database..object_name
- schema_name.object_name
- object_name
An object name that specifies all four parts is known as a fully qualified name. Each object that is created in Microsoft SQL Server 2005 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.
Most object references use three-part names. The default is the local server. Four-part names are generally used for distributed queries or remote stored procedure calls. They use the following format.
linkedserver.catalog.schema.object_name
The following 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 that is 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 Microsoft SQL Server 2005 can use as part of a Transact-SQL statement.
To determine what components in the OLE DB data source are used for the catalog and schema parts of the name, see the documentation for the OLE DB provider specified for the linked server. If the linked server is running an instance of SQL Server, the catalog name is the database that contains 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 that is accessed through the local server. Execute stored procedures on the remote server by using the following format for the procedure name:
server.database.schema_name.procedure
All four parts of the name are required when you are using a remote stored procedure. For more information about remote servers, see Configuring Remote Servers.
Referencing Columns by Using Identifiers
Column names must be unique within a table or view. You can use up to three prefixes to specify columns in a query where more than one table that is referenced may have a column of the same name. Any one of the following formats is acceptable:
database_name.schema_name.object_name.column_name
database_name..object_name.column_name
schema_name.object_name.column_name
object_name.column_name
Referencing Column Properties of User-Defined Type Columns
Properties of user-defined type columns are referenced by using the period separator (.) between the column name and the property name. When property names are referenced, you can also reference the table or view identifiers, but not the database or schema identifiers. The following formats are acceptable:
column_name.property_name1.property_name2…
object_name.column_name.property_name1.property_name2…
See Also
Other Resources
FROM (Transact-SQL)
Transact-SQL Syntax Conventions (Transact-SQL)