What is difference between sys.objects, sys.system_objects, and sys.sysobjects?

854 Asked by DylanPEREZ in SQL Server , Asked on Jul 12, 2021

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

What is SQL server SYS object ?

Answered by Daniel BAKER

SQL server SYS object :

Contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function. ... objects does not show DDL triggers, because they are not schema-scoped. All triggers, both DML and DDL, are found in sys. Here's the difference:

ys.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

    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.

What is difference between sys.objects, sys.system_objects, and sys.sysobjects?

Your Answer