In this question I was writing a query using sys.sysobjects
. However, one of the answers mentioned sys.system_objects
. I'm just wondering what is the difference between these tables?
sys.objects
sys.system_objects
sys.sysobjects
sysobjects
has more things.
> SELECT count(*) FROM sysobjects;
2312
> SELECT count(*) FROM sys.system_objects;
2201
> SELECT count(*) FROM sys.objects;
> 111
SELECT count(*)
FROM sys.sysobjects
WHERE NOT EXISTS (
SELECT 1
FROM sys.system_objects
WHERE system_objects.object_id = sysobjects.id
);
> 111
Best Answer
As noted in my previous post
sys.sysobjects
is deprecated:You would now have to combine
sys.system_objects
andsys.objects
to retrieve all items that are stored in the deprecatedsys.sysobjects
system table.Result:
sys.system_objects
sys.objects
For example
sp_MScleanupmergepublisher
isìs_ms_shipped
but not in thesys
schema (it is indbo
) so it's insys.objects
and notsys.system_objects
. This is possibly because it is a shell forsys.sp_MScleanupmergepublisher_internal
and is probably defined on instance creation. If you have SSMS right-click on thedbo.sp_MScleanupmergepublisher
system stored procedure and then select Modify: