locked
Differenece between sys.Objects and sysobjects in SQL Server 2005 ? RRS feed

  • Question

  • What is the actual differenece between sys.Objects and sysobjects in SQL Server 2005?

    select

     

    * from sys.objects;

    select

     

    * from sysobjects;

    I tried both queries on system and user databases as well and found that for every database the result is same but for Master database sysobjects is returning more rows than sys.objects.

    Need an expert comment...!

    Monday, September 6, 2010 1:39 PM

Answers

  • Hi Manish,

     

    According to Books Online (BOL), many of the system tables from earlier releases of SQL Server are now implemented as a set of views in SQL Server 2005. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000. However, the compatibility views do not expose any of the metadata related to features that are introduced in SQL Server 2005. Therefore, when you use new features, such as Service Broker or partitioning, you must switch to using the catalog views.

     

    For example, sysobjects is just a system table in SQL Server 2000. However, in SQL Server 2005, there is no such system table named “sysobjects”, it is implemented as a system view, just as background compatibility view only. Therefore, when we use new features, we need to switch to using the catalog views, here is sys.objects. In SQL Server 2005, since many of new features are used and stored in the system database, so these two views (sysobjects and sys.objects) return the result will certainly different.

     

    For more information, please visit the following links:

    Compatibility Views (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms187376(v=SQL.90).aspx

    Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views: http://msdn.microsoft.com/en-us/library/ms187997(v=SQL.90).aspx

    User-Schema Separation: http://msdn.microsoft.com/en-us/library/ms190387(v=SQL.90).aspx

     

    If you have any more questions, please feel free to let me know.

     

    Thanks,

    Yoyo Yu

    • Marked as answer by Manish K Singh Tuesday, September 7, 2010 1:34 PM
    Tuesday, September 7, 2010 7:49 AM

All replies

  • It is mentioned in the MSDN that sysobjects was the system table in SQL Server 2000 and included in later versions for backward compatibility but why then it is giving different results for Master database?

    Monday, September 6, 2010 1:50 PM
  • Hi Manish,

     

    According to Books Online (BOL), many of the system tables from earlier releases of SQL Server are now implemented as a set of views in SQL Server 2005. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000. However, the compatibility views do not expose any of the metadata related to features that are introduced in SQL Server 2005. Therefore, when you use new features, such as Service Broker or partitioning, you must switch to using the catalog views.

     

    For example, sysobjects is just a system table in SQL Server 2000. However, in SQL Server 2005, there is no such system table named “sysobjects”, it is implemented as a system view, just as background compatibility view only. Therefore, when we use new features, we need to switch to using the catalog views, here is sys.objects. In SQL Server 2005, since many of new features are used and stored in the system database, so these two views (sysobjects and sys.objects) return the result will certainly different.

     

    For more information, please visit the following links:

    Compatibility Views (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms187376(v=SQL.90).aspx

    Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views: http://msdn.microsoft.com/en-us/library/ms187997(v=SQL.90).aspx

    User-Schema Separation: http://msdn.microsoft.com/en-us/library/ms190387(v=SQL.90).aspx

     

    If you have any more questions, please feel free to let me know.

     

    Thanks,

    Yoyo Yu

    • Marked as answer by Manish K Singh Tuesday, September 7, 2010 1:34 PM
    Tuesday, September 7, 2010 7:49 AM