SQL Server recognizes up to four parts of object names. Depending on the context of an expression, some parts may or may not be necessary when referencing an object. When script runs on a different server or when using a different database, related object names may be required. Note that SQL Server 2005 recognizes the schema name in the third position, whereas SQL Server 2000 recognizes the object owner name in the same position. If you are using SQL Server 2000, substitute the owner for the schema.
Object Reference | Use and Context |
---|---|
object | In the context of the local database, on the same server. Object is owned by the dbo user (SQL Server 2000) or part of the dbo schema (SQL Server 2005) and there are no duplicate object names. |
schema.object | In the context of the local database, on the same server. Object may be owned by a user other than dbo (SQL Server 2000) or part of a specific schema. Duplicate object names that have different owners or schema names are permitted. Also uses a standard convention for clarity. |
database..object | In the context of the same or different database on the same server. Without specifying the owner or schema, assumes the dbo owner or schema. |
database.schema. object | A three-part name fully describes an object on the same server, in the same or different database. |
server.database. schema.object | A four-part name is valid in the context of a remote server or the |
server.database..object | The database owner or schema in the third position can be omitted to use the default dbo owner or schema. |
server..schema.object | The database name can be omitted to use the default database on that server. This is a not a typical practice. |
server...object | Omitting the database and owner or schema name uses the default database and the default dbo user or schema. This is not a typical practice. |