Problem
When referencing a SQL Server object, there are multiple levels that an object can be referenced, this is where SQL Server four part naming comes into play. In this article, we look at how to access a database object at each of these levels: object, schema, database and server.
Solution
SQL Server offers the functionality to reference objects within the database you are working or to reference objects in another database or even a different instance of SQL Server. This is referred to as four-part naming. The reason for this name is that there can be up to four parts to reference an object.
SQL Server four part naming
The four parts are defined as follows.
server.database.schema.objectObject – in SQL Server four part naming
In some cases, your SQL Server code may only reference the object name such as the following. In this example, this will look for the “sysobjects” table in the database you are working with.
SELECT * FROM sysobjectsSchema – in SQL Server four part naming
To further qualify this we can also specify the schema of the object.
Below, we are using the schema “dbo” along with the table “sysobjects”. This is helpful in case there are two different objects with the same name in a database. The schema creates that division. Again, this will look for this object in the database you are working with.
SELECT * FROM dbo.sysobjectsDatabase – in SQL Server four part naming
To take this a step further we can also reference the database that the object resides in as follows. This lets us know reference objects in another database regardless of what database we are working in.
SELECT * FROM master.dbo.sysobjectsServer – in SQL Server four part naming
And lastly, we can reference this table on a totally different server by specifying the server name. You would need to have a linked server setup to reference on an object on a totally different instance of SQL Server or some other database source that has been configured via a linked server.
SELECT * FROM test1.master.dbo.sysobjectsSummary
The ability to reference an object this way opens up several possibilities whether it be within the same database or on other databases or other servers. One example is the use of the schema part. This alone allows you to have the same object name in your database several times and with the owner or schema reference you can now signify which object you actually want to use in your code.
Also, if you have a standard database that may be used by multiple databases, by using the database part you can now have multiple databases pull in this standard data by using the database part of the four-part name.
And lastly, you can even separate your data even further by using the fourth part of the name that references the server. One thing to note is that you will need to setup linked servers in order to use this option. Another thing to note is that there may be some performance issues when using linked servers especially if you are querying a lot of data.
Next Steps
- Take a look at your code to see if there are areas that could benefit from this four-part naming structure
- Look at the use of synonyms in SQL Server to simplify four-part naming
- Also, take a look at ownership chaining and security/permission issues
Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.
