SQL 2005+SQL Server Multipart Names
SQL Server-based applications and systems may include multiple schemas, databases and linked servers, all of which are accessible from scripts and stored procedures. To allow the correct database objects to be accessed requires the use of multipart names.
Multipart Names
In the SQL Server Programming Fundamentals tutorial and in other SQL Server articles available through this web site, most of the examples to date use simple, single part naming. For example, the following SELECT statement retrieves information from a table named, "TestTable". The table's name is provided as a single part name.
In most situations this is a simple and readable way to identify the database object that you wish to access. However, there are several situations that require that you use a multipart name instead.
Specifying a Schema
All tables and other database objects are created within a schema. In basic terms, schemas allow you to organise items into logical groups. You may decide to create all of your live tables, views and stored procedures within the default schema for your user, which is probably the dbo schema on your development system. You may wish to organise your archived data in a separate schema, perhaps named "archive". Such a system would allow the archive and live tables to have the same names because they are in different schemas.
When you need to access a database object that is not in your default schema, you must provide the schema name before the object name, separating the two items with a full stop (period). This is a two part name. For example, to read the archived version of TestTable, you might use the following query.
SELECT * FROM archive.TestTable
Specifying a Database
If your software uses more than one database, you may need to make calls across database boundaries. This requires a three part name. The first part specifies the target database, the second the schema and the third the object to be accessed.
SELECT * FROM TestDB.archive.TestTable
When using a three part name, the schema can be omitted. However, the two full stop characters are still required. The following example retrieves all of the data from the TestDB database's TestTable table within the default schema for the user.
SELECT * FROM TestDB..TestTable
Accessing Remote Database Objects
The final and most complex naming option is the four part name. This extends the three part version by adding the name of a SQL Server instance as the first part. This allows you to access database objects defined within a remote SQL Server. This only works if the server executing the query has been linked to the remote instance.
The last example reads the TestTable in the archive schema of the TestDB database that resides on the TestServer instance.
SELECT * FROM TestServer.TestDB.archive.TestTable
6 March 2013