Last updated on September 28th, 2022 at 09:33 am
The purpose of this blog post is to help you identify the four possible parts of a SQL Server object. System Center Configuration Manager (SCCM) reports are created by querying the SCCM database. Most often SQL Server Reporting Services (SSRS) is used to display SCCM reports. When querying the SCCM database, SQL Server Query Language, oftentimes called T-SQL, is used.
T-SQL queries generally use a two-part SQL Server view name. This is found within the From section of your query. Here’s an example of a two-part SQL Server view name: dbo.v_R_System_Valid.
By now you are probably asking yourself, “Why is this important?” The answer is linked servers. Using linked servers will allow you to query a cloud-hosted SQL Server or any remote database server. In order to query a cloud-hosted SQL Server you will need to write queries using four-part SQL Server objects.
Would you like to create a dashboard using data from your cloud server? I’ll show you how to get started in my next blog post. First, though, I felt that I needed to explain the four parts of a SQL Server object.
Before getting too far ahead, I want to clarify a couple of terms.
SQL Server View(s) and SQL Server Object(s)
In T-SQL, SQL Server object is a general term that identifies any of the following: SQL Server views, tables, functions, stored procedures, etc.
In short, all SQL Server views are SQL Server objects, but not all SQL Server objects are views.
Four-Part Names of a SQL Server Object
The four parts of an SQL object are:
1. Server Name – Database server name or server alias name.
2. Database Name – Database name.
3. Schema Name – Owner of the object.
4. Object Name – Name of the object.
An example of a four-part SQL Server object is:
CM-CAS-CB1.CM_CB1.dbo.v_R_System_Valid
Below you’ll see that I’ve matched the different parts from my example:
1. CM-CAS-CB1
2. CM_CB1
3. dbo
4. v_R_System_Valid
Additionally, to uniquely identify an object within SQL Server, you need all four parts: Server Name (1), Database Name (2), Schema Name (3) and Object Name (4). As I have shown in the screenshot above.
If you attend any of my sessions on report writing, you will almost always see me ONLY use a two-part name when querying results from the SCCM database. You might still be wondering at this point why I’m talking about a four-part name query when a two-part name query seems to do the job.
During presentations, when I connect to SQL Server using SQL Server Management Studio (SSMS), I specify the Server Name (1). Therefore when querying that server, I don’t need to provide that information within the SQL Server query. It is assumed that I want to query that server.
In the first screenshot in this blog post, you will notice that I included the Server Name [CM-CAS-CB1]. In the next screenshot (the one above) you will see that the gold arrow shows you that the server name was removed. The query, however, still works correctly!
Removing the Server Name drops me down to three identifiable SQL Server objects. What one is removed next?
Two Part Names
When the database is already selected it is now possible to use two names to identify an object in your SQL Server query. As can be seen in the screenshot above that the default database is set to CM_CB1 (purple arrow).
While, in the screenshot above the Database Name (2) was removed from the query (gold arrow).
Now you know why you will only see me use two-part name queries in my presentations.
WAIT!!! Why, Garth, does this work?
Basically, the short answer is that SQL Server allows administrators to be lazy. SQL Server will search all database schema (3) to find the SQL Server object, BUT this is truly bad form!
Bad Form Query
Here’s an example of what I mean by bad form.
But you can have two tables! as shown above both tables are called System_Disc2. Notice that within the red box they are both listed (dbo and GMJ). Also notice that the purple boxes show the count of rows for dbo.System_DISC2. Whereas the green boxes show the count of rows for GMJ.System_DISC2.
Consequently, lazy SQL Server Admin queries only for System_DISC2. Which table is it? Was that the one they were meant to query? You can see above why it is important to, at a bare minimum, always use the Schema Name (3) and Object Name (4) when querying items within your database.
For more details about four-part names, review the Multipart Names section within the SQL Server Books online.
Further, do you have an idea for a blog post about a ConfigMgr query or reporting topic? Let me know. Your idea might become the focus of my next blog post! Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, If you have any questions, please feel free to touch base @Garthmj.