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