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

Question

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

Answer 1

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

Answer 2

Here's the difference:

• sys.sysobjects is an old SQL Server 2000 system table (compatibility view). Starting SQL Server 2005, Microsoft introduced a new set of views called catalog views as a replacement to compatibility views. Compatibility views are still in SQL Server for backward compatibility (Microsoft decided to leave the old views to not break some internal codes).

• sys.system_objects is a catalog view. You can check the object definition by executing this: SELECT OBJECT_DEFINITION(OBJECT_ID('sys.system_objects'))

Inside the catalog view you can see they are using the system table sys.sysschobjs:

CREATE VIEW sys.system_objects 
AS   
SELECT o.name,    
o.id AS object_id,    
convert(int, null) AS principal_id,    
o.nsid AS schema_id,    convert(int, 0) AS parent_object_id,    
o.type,    
n.name AS type_desc,    
o.created AS create_date,    
o.modified AS modify_date,    
convert(bit, 1) AS is_ms_shipped,    
convert(bit, 0) AS is_published,    
convert(bit, 0) AS is_schema_published   
FROM sys.sysschobjs o   
LEFT JOIN sys.syspalnames n ON n.class = 'OBTY' AND n.value = o.type   WHERE has_access('SO', o.id) = 1 

Extracting from sys.objects you can find the sys.sysschobjs

SELECT name, type_desc FROM sys.objects
WHERE name = 'sysschobjs' 

The system table below needs DAC to access

+------------+--------------+
| name       | type_desc    |
+------------+--------------+
| sysschobjs | SYSTEM_TABLE |
+------------+--------------+

You can find the catalog views by executing:

SELECT * 
FROM sys.all_views
WHERE [schema_id] = 4 AND [name] NOT LIKE 'dm%' AND [object_id] NOT IN (-212,-211,-210,-209,-208,-207,-206,-205,-204,-203,-202,-201,-200,-199,-198,-197,-196,-195,-194,-193,-192,-143,-142,-141,-140,-139,-138,-137,-136,-135,-134,-133,-132,-131,-130,-129,-106,-105)
ORDER BY [name] ASC

And Microsoft docs here

you can check out other system tables and compare like:

• sys.databases (SQL Server 2005 and up)
• sys.sysdatabases (SQL Server 2000)

Microsoft doesn't encourage us to use the old system tables. The old compatibility views don't have the new metadata related to higher version of SQL Server features (eg. partitioning, etc). Only use it on SQL Server 2000 as Microsoft will remove it from future release of SQL Server.

Answer 3

Microsoft Parlance

To start Microsoft has three categories relevant to this discussion,

• System Base Tables, SYSTEM TABLE, or Catalog Tables which store "System Objects". These require DAC for direct access.
• System Catalog Views which has numerous named subcategories in the Microsoft diction, the only one relevant to this discussion is Object Catalog Views. INFORMATION_SCHEMA can be thought of as the ISO standardized System Catalog View.
• Compatability Views, strictly speaking a legacy layer whose use is explicitly deprecated.

Application

• sys.sysschobjs, SYSTEM TABLE, all objects in SQL Server are represented in this System Table. This system table has it's own view called sys.sysschobjs$ which takes the internal bit strings and expands them into bit columns. Two Catalog Views that pull from sys.sysschobjs$ joining against sys.syspalnames ON syspalnames.class = 'OBTY' are,

  • sys.system_objects, OBJECT CATALOG VIEW. Decreed by Microsoft, designated as System, and identified by their installation into the sys schema. They get an explicit "System" qualifier in the parlance. Internally this is has_access('CO', o.id)

  • sys.objects, OBJECT CATALOG VIEW. All other objects are Non-System objects, which is implicit. When you see objects (juxtaposed with System Objects), think non-System Objects. Internally this is has_access('SO', o.id)

• sys.sysobjects, Compatability View. Deceptively named because the sys designation here is awkward when the view itself pulls in non-sys objects. I suspect at one time there was no designation between system and non-system objects and they were all sysobjects. Now that the designation is there this has become a "Compatibility View". Though other answers imply that this is a UNION ALL that is strictly speaking incorrect.

  • It queries sys.sysschobjs directly and provides some information the other Catalog Views have dropped, like uid.
  • Internally this is has_access('MO', id) (not yet sure of ramifications)
  • type, userstat, and systat are all added here, and what the Compatibility View calls category is supremely weird and possibly not used anywhere else. xtype is the unadulterated type from sys.sysschobjs$

Footnotes

I believe the sch in sysschobjs stands for schema. That's another anti-mnemonic, forget it because it stores things not in the sys schema. For that you need sys.system_objects.