Making sense of sys.objects, sys.system_objects, and sys.sysobjects?

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

As noted in my previous post sys.sysobjects is deprecated:


Note taken from sys.sysobjects (Transact-SQL)


This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

You would now have to combine sys.system_objects and sys.objects to retrieve all items that are stored in the deprecated sys.sysobjects system table.

SELECT * FROM sys.system_objects 
UNION ALL
SELECT * FROM sys.objects AS o

Result:

(2171 row(s) affected)

-
sys.system_objects


Contains one row for all schema-scoped system objects that are included with Microsoft SQL Server. All system objects are contained in the schemas named sys or INFORMATION_SCHEMA.

-
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

For example sp_MScleanupmergepublisher is ìs_ms_shipped but not in the sysschema (it is in dbo) so it's in sys.objects and not sys.system_objects. This is possibly because it is a shell for sys.sp_MScleanupmergepublisher_internal and is probably defined on instance creation. If you have SSMS right-click on the dbo.sp_MScleanupmergepublisher system stored procedure and then select Modify:

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_MScleanupmergepublisher]    Script Date: 19.12.2017 12:12:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[sp_MScleanupmergepublisher]
as
    exec sys.sp_MScleanupmergepublisher_internal

StackExchange Database Administrators Q#193505, answer score: 13