HEELPBOOK - 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!


############ ARTICLE INFO #############

Article Month: June
Article Date: 02/07/2012
Permalink: http://heelpbook.altervista.org/2012/sql-server-four-part-object-names/
Source: http://sqlandme.com/2012/04/30/sql-server-four-part-object-names/
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook