Printed 2017/08/21 03:48PM

SQL Server – Four-part object names


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!


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

Filed under: Catalog Views, SQLServer
Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.