Difference between sys.objects & sys.all_objects

Question

Difference between sys.objects & sys.all_objects

pshaship

SSCertifiable

Points: 6125
More actions
September 27, 2010 at 4:59 pm

#227085

Hi,
What is the difference between the results displayed by sys.objects & sys.all_objects
I have executed the below in AdventureWorks database:
select * from sys.objects -->Returns 575 row(s)
select * from sys.all_objects--> Returns 2503 rows
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

pshaship (9/27/2010)
Hi,
What is the difference between the results displayed by sys.objects & sys.all_objects
I have executed the below in AdventureWorks database:
select * from sys.objects -->Returns 575 row(s)
select * from sys.all_objects--> Returns 2503 rows
Thanks

Sys.Objects are all objects within a database

Sys.Object are all objects within a SQL Server

For more info check:

SYS.Objects >>> http://tinyurl.com/38o84e4

vs

SYS.All_Objects >>> http://tinyurl.com/36lenbj

Hope it helps!

😉

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 2

pshaship (9/27/2010)
Hi,
What is the difference between the results displayed by sys.objects & sys.all_objects

Very simply, whether system objects are included or not.

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

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

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Joe Ferry SSC Enthusiast Points: 113 More actions · Answer 3

If by SQL Server, you mean all databases within an instance of SQL Server, this is not the right answer. (This is a reply to the answer saying that sys.all_objects refers to objects within a SQL Server.)

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 4

sysobjects uid gives you the uid of the owner of the object.

uid is the schema ID of the owner of the object that is equal to the user ID of the owner.

MMartin1 One Orange Chip Points: 27799 More actions · Answer 5

From MSDN >

sys.objects

Contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function.

Notice it says "within a database."

Further,

sys.all_objects

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

I see nothing that would indicate it is all objects in all database. These are for the database you are currently working in. It just happens that when a new database gets created the same objects that are in the model database are the framework for the new database. So it seems like you are seeing the same non user defined object and object ID across databases but are each their own instance.

----------------------------------------------------