sysobjects

  • Comments posted to this topic are about the item sysobjects

  • sysobjects is a system table and sys.sysobjects and sys.objects are views. Question mentions sysobjects.

    "Keep Trying"

  • I thought the same....

    Run

    select objectproperty(object_id('sysobjects'), 'IsView')

    and you'll see it's a view 🙁

  • This one caught me too. In 2000, it was a table but they switched it to a view in 2005 for backwards compatibility. The question specifically asks about 2005.

  • Chirag (8/28/2009)


    sysobjects is a system table and sys.sysobjects and sys.objects are views. Question mentions sysobjects.

    Nope.

    Until SQL Server 2000, sysobjects was a system table. As of SQL Server 2005, system servers are no longer accessible, and their names and layout are therefor no longer published. They were replaced by the "true system views", with an all new design - of which sys.objects is an example. And to retain backwards compatibility, another set of system views, aka compatibility views, was introduced with the names of the old system tables. The question mentions sysobjects, which is one of those compatibility views.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Chirag (8/28/2009)


    sysobjects is a system table and sys.sysobjects and sys.objects are views. Question mentions sysobjects.

    Partially correct in regard to the question as the question specifies SQL Server 2005 as well. In SQL Server 2005 sysobjects is a view.

  • cengland0 (8/28/2009)


    This one caught me too. In 2000, it was a table but they switched it to a view in 2005 for backwards compatibility. The question specifically asks about 2005.

    Learn something new everyday...I knew about the sys.objects was a view...didn't know they made a compatibility view named sysobjects. Thanks for the info.

  • sysobjects are system tables.. if u query systables u can see that these are system tables in 2005

  • vinuraj (8/28/2009)


    sysobjects are system tables.. if u query systables u can see that these are system tables in 2005

    are you sure about that?

    Unless I am misunderstanding your post I think that sysobjects and sys.sysobjects are views based on the below reference

    http://msdn.microsoft.com/en-us/library/ms187376(SQL.90).aspx

    please correct me if I am wrong

  • vinuraj (8/28/2009)


    sysobjects are system tables.. if u query systables u can see that these are system tables in 2005

    SELECT * FROM systables;

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'systables'.

    SELECT * FROM sys.systables

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sys.systables'.

    SELECT name FROM sys.tables;

    name

    -------------------

    spt_fallback_db

    spt_fallback_dev

    spt_fallback_usg

    spt_monitor

    spt_values

    MSreplication_options

    SELECT name, xtype FROM sysobjects WHERE name = 'sysobjects';

    name xtype

    ------------ -----

    sysobjects V

    SELECT @@VERSION;

    Microsoft SQL Server 2005 - 9.00.4035.00


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Great question and I got it wrong. Made me do some research and found that it is indeed a system View. Keep these great questions coming as you learn more from your mistakes. 🙂

    Steve

  • this was caught me at work the other day. you can still actually directly access the system table by toggling some sp_configure property i believe, could be wrong though, although there shouldn't be a need

  • actually you can just access the system tables directly, ie dbo.sysobjects

  • Hi Daggles,

    Yes, you can access sysobjects and other "system table" directly - except that they are in fact no longer system tables, but views. The real system tables are completely hiddden from view and can normally not be accessed. (If I recall correctly, I once read in Kalen Delaney's books how you can access these tables if you really want to, but it's a complicated workaround).

    The sp_configure option you refer to pre-dates SQL Server 2005. Up until SQL Server 2000, sysobjects and other such tables were in fact actual tables. And you could not only query them, you could even update their contents directly - but only if you first toggled this sp_configure setting from the "safe" default position to the "allow me to shoot me in my foot" position. This sp_configure option still exists, to reduce amount of code changes required for upgrades, but it has no effect anymore. Regardless of the toggle's position, any attempt to update sysobjects or other system tables or system views will always result in an error.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Right this is View

    you can check

    sp_help sysobjects

    😛

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply