Home > Catalog Views, SQLServer > SQL Server – Four-part object names

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!

Vishal

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

About these ads
Categories: Catalog Views, SQLServer
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 266 other followers

%d bloggers like this: