This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
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