August 6, 2019 at 11:04 pm
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.
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?
August 7, 2019 at 11:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 8, 2019 at 7:34 am
This was removed by the editor as SPAM
August 8, 2019 at 7:37 am
This was removed by the editor as SPAM
August 8, 2019 at 4:32 pm
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);
August 13, 2019 at 1:21 am
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?
August 13, 2019 at 2:42 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply