sys.Objects$ - Any way to see it?

Question

sys.Objects$ - Any way to see it?

Jeff Moden

SSC Guru

Points: 1004434
More actions
January 2, 2008 at 1:00 am

#182268

Forgive me for what may be a really stupid question... I've been stuck in the world of SQL Server 2000 for a very long time and only recently have had the opportunity to load SQL Server 2005 to play with... I'm logged in to 2k5 with "SA" privs...
If we execute the following snippet of code...
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.Objects'))
... we see that the schema-scoped view for sys.Objects has the following T-SQL in it...
CREATE VIEW sys.objects AS
SELECT name, object_id, principal_id, schema_id, parent_object_id,
type, type_desc, create_date, modify_date,
is_ms_shipped, is_published, is_schema_published
FROM sys.objects$
Now, being ever curious, I wanted to see what sys.objects$ actually had in it. But when I execute the following code...
SELECT * FROM sys.objects$
... I get the following error message...
[font="Courier New"]Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.objects$'.[/font]
So... my questions are, is it possible to directly examine the contents of sys.objects$ and if so, how? Or, has MS made it totally impossible to do such a thing?
Thanks for the help, folks...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can read the contents of the system tables if you connect using the DAC. They're not that interesting and are quite hard to understand, since there's no documentation anywhere. They're not really hidden, it's just that the parser won't bind to any of the system tables.

I suspect that the definition of sys.objects$ (if it's not a alias for something else) is actually in the system resource database.

You can't usually connect to the system resource db, but it is possible to make yourself a copy of it.

Shut down your SQL instance and find the files mssqlsystemresource.mdf and mssqlsystemresource.ldf. Make a copy of them, restart SQL and attach the copied files as a user database. (Or if you like, I can send you the ones I have)

I took a look at some a while back while trying to find the stats blob. A lot of the system views (especially the DMVs) just refer to (I assume) internal structures using a ROWSET operator

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

Perfect... thanks, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Pleasure

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736603 More actions · Answer 4

Great explanation and I'd add that they did a great job with the DMVs exposing almost everything you need, so you don't really need to hit the base table.

Plus the DMVs should be supported for much longer than the system tables.