sys.system_objects, objects and all_objects

Question

sys.system_objects, objects and all_objects

michael.leach2015

SSCommitted

Points: 1597
More actions
August 6, 2019 at 11:04 pm

#3669322
I ran the sys.system_objects, sys.objects and sys.all_objects views in the same database. The results I got were not at all what I expected to see.
1. There were 2,200 objects in sys.system_objects.
  1. All appeared in sys.objects (not what I expected)
  2. sys.all_objects (I did expect this)
There were 2,310 objects in sys.objects
1. 2,200 of them appear in sys.system_objects (I didn't expect any would appear in sys.system_objects because I believe that sys.objects refers to user-defined objects only, but I guess I might be wrong).
2. All appeared in sys.all_objects (which is what I expected)
There were 2,310 objects in sys.all_objects
1. 2,200 of them appear in sys.system_objects (I didn't expect any would appear in sys.system_objects because I believe that sys.objects refers to user-defined objects only, but I guess I might be wrong).
2. All appeared in sys.objects (which is what I expected)

If sys.system_objects refers only to system objects and sys.objects refers only to user-defined objects, then these two added should be what is in sys.all_objects right?

But apparently I'm not seeing this the right way. What am I missing?

Attachments:

You must be logged in to view attached files.

Site Owners SSC Guru Points: 80342 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jason A. Long SSC-Insane Points: 23789 More actions · Answer 2

sys.all_objects should in fact be the UNION ALL of system objects (sys.system_objects... object_id < 0) and user objects (sys.objects... object_id > 0).

If you are seeing something other than this, please execute the following and post the results...

SELECT 
    *
FROM
    sys.all_objects ao
WHERE 1 = 1
    AND NOT EXISTS (SELECT 1 FROM sys.system_objects so WHERE ao.object_id = so.object_id)
    AND NOT EXISTS (SELECT 1 FROM sys.objects o WHERE ao.object_id = o.object_id);

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 3

Jason A. Long wrote:

sys.all_objects should in fact be the UNION ALL of system objects (sys.system_objects... object_id < 0) and user objects (sys.objects... object_id > 0).
If you are seeing something other than this, please execute the following and post the results...
SELECT 
    *
FROM
    sys.all_objects ao
WHERE 1 = 1
    AND NOT EXISTS (SELECT 1 FROM sys.system_objects so WHERE ao.object_id = so.object_id)
    AND NOT EXISTS (SELECT 1 FROM sys.objects o WHERE ao.object_id = o.object_id);

Jason,

Sounds like you are saying that all system objects will have an object ID that is a negative number, and all objects created by the user will have an Object ID that is a positive number. Is this true to say?

The reason I ask is because I queried all columns on sys.objects and while the object IDs are all positive numbers, in the type description column I am seeing things like "System Table," "Internal Table" and "Service Queue." These don't sound like a user created object. In fact, there are 110 items and the vast majority of these I did not even create. It seems like the sys.objects view is showing the few objects I have created in the current database, in addition to many other objects which I sound to me like they would be classified as system objects. It sounds like I am misunderstanding the definition of what a user object is. I am interpreting a user object to mean any object that I have created such as a table, view, primary key, etc.

Also, I tried running the query you suggested and it didn't return any rows at all. Was this the expected result?

Jason A. Long SSC-Insane Points: 23789 More actions · Answer 4

No, I'm not saying that at all. "All collies are dogs but not all dogs are collies"... While it is true that all objects with an object_is < 0 are system objects, not all system objects have an object_id < 0. Some system objects do in fact have positive values (typically under 10,000).

If you need to differentiate between system objects and user created objects, look at the schema_id and is_ms_shipped. I don't know that MS has any hard & fast rules that would guarantee the following... but... from what I've seen, MS uses schema_ids 3 & 4 (sys & INFORMATION_SCHEMA) exclusively and users cannot create objects in either. It's also been my experience that all system objects have is_ms_shipped = 1. Just keep in mid that there is and undocumented system procedure that will allow a user to set that flag on user created objects (sys.sp_MS_marksystemobject), so that may not be 100%.

As for the query results, you are correct. It was not supposed to return any results. I posted it because your OP made it sound like things weren't adding up. Had it returned any results, it would have really peaked my curiosity.

sys.system_objects, objects and all_objects

Attachments:

Cookies on SQLServerCentral