Generally it is considered good practice using two-part names for objects. It make the code more readable and avoids confusion if objects with similar names exists in different schemas. Some features requires that two-part naming must be used such as creating a view WITH SCHEMABINDING.

However, you can also use a four-part name to refer objects. Four-part object reference format consists of:

<< Server.Database.Schema.Object >> 

Only object name is required to reference an object, (schema name when the object is not in user’s default schema) all else is optional.

For example, all statements below will work in SQL Server:

USE   msdb

/*    Four-part name */

SELECT Name FROM VGAJJAR2.msdb.dbo.backupset

/*    Server name omitted */

SELECT Name FROM msdb.dbo.backupset

/*    Server/database name omitted */

SELECT Name FROM dbo.backupset

/*    Server/database/schema name omitted,

      only default schema name can be omitted */

SELECT Name FROM backupset

/*    And this works too… */

SELECT Name FROM …backupset

SELECT Name FROM ..dbo.backupset

SELECT Name FROM .msdb.dbo.backupset

SELECT Name FROM .msdb..backupset

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Filed under: Catalog Views, SQLServer