If you’ve used T-SQL for a while, it’s likely you’ve encountered the sys.objects system catalog view, which returns user-defined, schema-scoped objects in the database.

But there are two other similar views that you may or may not be aware of; sys.system_objects and sys.all_objects.

Although similar, their differences are clear, and the names tell the difference. Here’s the official definition all three views:

sys.objects

Contains a row for each user-defined, schema-scoped object that is created within a database.

sys.system_objects

Contains one row for all schema-scoped system objects that are included with SQL Server.

sys.all_objects

Shows the UNION of all schema-scoped user-defined objects and system objects.

In other words, the last view combines the results of the previous two views (it returns both system and user-defined objects).

Example

Here’s an example that demonstrates the difference in results returned by these views.

USE WideWorldImportersDW;

SELECT COUNT(*) AS [sys.objects]
FROM sys.objects;

SELECT COUNT(*) AS [sys.system_objects]
FROM sys.system_objects;

SELECT COUNT(*) AS [sys.all_objects]
FROM sys.all_objects;

SELECT 
  (SELECT COUNT(*) AS [sys.all_objects] FROM sys.all_objects) - 
  (SELECT COUNT(*) AS [sys.system_objects] FROM sys.system_objects) 
AS [sys.all_objects MINUS sys.system_objects];

Result:

+---------------+
| sys.objects   |
|---------------|
| 224           |
+---------------+
(1 row affected)
+----------------------+
| sys.system_objects   |
|----------------------|
| 2205                 |
+----------------------+
(1 row affected)
+-------------------+
| sys.all_objects   |
|-------------------|
| 2429              |
+-------------------+
(1 row affected)

These views return too many rows for me to list them out here, so I’m using COUNT() to return the number of rows that each one returns.

If you’re good at maths, you’ll quickly see that the count of sys.all_objects is the sum of sys.objects and sys.system_objects.

Here’s a query that does that addition.

SELECT 
  (SELECT COUNT(*) AS [sys.objects] FROM sys.objects) + 
  (SELECT COUNT(*) AS [sys.system_objects] FROM sys.system_objects) 
AS [sys.objects PLUS sys.system_objects];

Result:

+---------------------------------------+
| sys.objects PLUS sys.system_objects   |
|---------------------------------------|
| 2429                                  |
+---------------------------------------+

Note that the visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission.