Qualifying Table Names

When accessing tables that exist in your Oracle user account, the table can be selected simply by its unqualified name. Accessing tables in other Oracle schemas requires that the schema name be prefixed to the table name with a single period (.). Oracle synonyms can provide additional location transparency.

Microsoft SQL Server uses a different convention when it references tables. Because one SQL Server login account can create a table by the same name in multiple databases, the following convention is used to access tables and views: [[database_name.]owner_name.]table_name

Accessing a table in…	Oracle	Microsoft SQL Server
Your user account	SELECT * FROM STUDENT	SELECT * FROM USER_DB.STUDENT_ ADMIN.STUDENT
Other schema	SELECT * FROM STUDENT_ADMIN.STUDENT	SELECT * FROM OTHER_DB.STUDENT_ ADMIN.STUDENT

Here are guidelines for naming Microsoft SQL Server tables and views:

Using the database name and username is optional. When a table is referenced only by name (for example, STUDENT), SQL Server searches for that table in the current user’s account in the current database. If it does not find one, it looks for an object of the same name owned by the reserved username of dbo in the database. Table names must be unique within a user’s account within a database.
The same SQL Server login account can own tables with the same name in multiple databases. For example, the ENDUSER1 account owns the following database objects: USER_DB.ENDUSER1.STUDENT and OTHER_DB.ENDUSER1.STUDENT. The qualifier is the database username, not the SQL Server login name, because they do not have to be the same.

At the same time, other users in these databases might own objects by the same name:

USER_DB.DBO.STUDENT
USER_DB.DEPT_ADMIN.STUDENT
USER_DB.STUDENT_ADMIN.STUDENT
OTHER_DB.DBO.STUDENT

Therefore, it is recommended that you include the owner name as part of the reference to a database object. If the application has multiple databases, it is recommended that the database name also is included as part of the reference. If the query spans multiple servers, include the server name.

Every connection to SQL Server has a current database context, set at login time with the USE statement. For example, assume the following scenario:
A user, using the ENDUSER1 account, is logged in to the USER_DB database. The user requests the STUDENT table. SQL Server searches for the table ENDUSER1.STUDENT. If the table is found, SQL Server performs the requested database operation on USER_DB.ENDUSER1.STUDENT. If the table is not found in the ENDUSER1 database account, SQL Server searches for USER_DB.DBO.STUDENT in the dbo account for that database. If the table is still not found, SQL Server returns an error message indicating the table does not exist.
If another user, for example DEPT_ADMIN, owns the table, the table name must be prefixed with the database user’s name (DEPT_ADMIN.STUDENT). Otherwise, the database name defaults to the database that is currently in context.
If the referenced table exists in another database, the database name must be used as part of the reference. For example, to access the STUDENT table owned by ENDUSER1 in the OTHERDB database, use OTHER_DB.ENDUSER1.STUDENT.

The object’s owner can be omitted by separating the database and table name by two periods. For example, if an application references STUDENT_DB..STUDENT, SQL Server searches as follows:

STUDENT_DB.current_user.STUDENT
STUDENT_DB.DBO.STUDENT

If the application uses only a single database at a time, omitting the database name from an object reference makes it easy to use the application with another database. All object references implicitly access the database that is currently being used. This is useful when you want to maintain a test database and a production database on the same server.