How is it 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 - To solve SQL server sys objects you should note that 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