sysobjects vs system_objects

Question

sysobjects vs system_objects

FreeHansje

SSChampion

Points: 11751
More actions
June 9, 2008 at 5:26 am

#183194

On a SQL2005 I can see in MASTER a view sysobjects and a view system_objects. I can adress sysobjects with a select, but when I do the same with system_objects I get an error telling me the object does not exists!?
However, when I adress the latter with SELECT * from SYS.system_objects, then I do get a result. Both views belong to schema SYS. Why the difference in behaviour? I have SA-rights on the server.
Greetz,
Hans Brouwer

Viewing 4 posts - 1 through 4 (of 4 total)

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

FreeHansje (6/9/2008)
On a SQL2005 I can see in MASTER a view sysobjects and a view system_objects. I can adress sysobjects with a select, but when I do the same with system_objects I get an error telling me the object does not exists!?
However, when I adress the latter with SELECT * from SYS.system_objects, then I do get a result. Both views belong to schema SYS. Why the difference in behaviour? I have SA-rights on the server.

dbo.system_objects does not exist. All new system views are owned by sys and not dbo

sys.system_objects

http://msdn.microsoft.com/en-us/library/ms173551.aspx

sys.objects (sys.sysobjects is for backward comp)

http://msdn.microsoft.com/en-us/library/ms190324.aspx

system_objects are all system objects (sys / information_schema owned).

sysobjects are all database objects.

You should be able to immediately see the difference in the objects stored by querying both:

SELECT * FROM sys.sysobjects

SELECT * FROM sys.system_objects

FreeHansje SSChampion Points: 11751 More actions · Answer 2

Tnx for answreing, but:

Where is the object dbo.sysobjects? A unqualified select, or even a qualified select on this objects gives me a resultset, but I can't see it anywhere in MASTER.

Greetz,
Hans Brouwer

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

FreeHansje (6/10/2008)
Tnx for answreing, but:
Where is the object dbo.sysobjects? A unqualified select, or even a qualified select on this objects gives me a resultset, but I can't see it anywhere in MASTER.

That is a legacy object ... It is still available in 2005, but it is actually referenced by using sys.sysobjects. Not sure how they do it ... but I do not believe you can see it in the gui. You should be using sys.objects moving forward.