http://www.sqlservercentral.com/blogs/sqlandme/2012/04/30/sql-server-four-part-object-names/

Printed 2014/07/24 05:26PM

SQL Server – Four-part object names

2012/04/30

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
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.