August 27, 2009 at 10:58 pm
Comments posted to this topic are about the item sysobjects
August 28, 2009 at 1:57 am
sysobjects is a system table and sys.sysobjects and sys.objects are views. Question mentions sysobjects.
"Keep Trying"
August 28, 2009 at 3:40 am
I thought the same....
Run
select objectproperty(object_id('sysobjects'), 'IsView')
and you'll see it's a view 🙁
August 28, 2009 at 5:05 am
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.
August 28, 2009 at 7:26 am
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.
August 28, 2009 at 8:02 am
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.
August 28, 2009 at 8:03 am
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.
August 28, 2009 at 8:07 am
sysobjects are system tables.. if u query systables u can see that these are system tables in 2005
August 28, 2009 at 8:20 am
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
August 28, 2009 at 8:52 am
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
August 28, 2009 at 9:27 am
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
August 31, 2009 at 7:01 pm
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
August 31, 2009 at 7:08 pm
actually you can just access the system tables directly, ie dbo.sysobjects
September 1, 2009 at 1:21 am
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.
September 4, 2009 at 12:00 am
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