2

I'm using this stored procedure called OBJECT_DEFINITION as was suggested. It's helping me wrap my head around the SQL Server system. However, there is one area that I can't yet figure out. On this one table sys.objects, you'll see it pulls from itself.

1> SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'))
2> GO

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE VIEW sys.objects AS
    SELECT name, 
      .. stuff.
    FROM sys.objects$

Now I'm totally lost how is sys.objects selecting from sys.objects$ what is sys.objects$. Thinking there may be a table of the same name, I first tried to create a table and a view by the same name, but I can't do that. I get (f is the name I picked). You can't do this in any database I've ever used.

Msg 2714, Level 16, State 3, Server x230, Procedure f, Line 1
There is already an object named 'f' in the database.

Microsoft docs sys.objects only as a Catalog View, it's not listed as a Base Table

The counterpart Catalog View, sys.system_objects pulls from the Base Table sys.sysschobjs, and OBJECT_DEFINITION clearly shows that.

My guess is that the SQL here isn't the actually SQL the view is executing, but some kind of comment or description on it: meta-data of sort, and that it's bugged. But, I could be totally off.

0

1 Answer 1

3

sys.objects is not a table, it is a system catalog view. The definition shown in the question even shows Create VIEW .... Also, sys.objects is not the same thing as sys.objects$:

  1. You cannot select from sys.objects$ on a regular connection. You need to be on a DAC connection, though not necessarily in Single-User mode, to SELECT from it.
  2. They don't have the same number of columns. Just connect to the DAC connection and run the following:

    SELECT TOP (1) * FROM sys.objects;
    GO
    SELECT TOP (1) * FROM sys.objects$;
    GO
    

sys.objects$ must be an internal catalog view since the underlying fields in sys.objects (no trailing $) come from these tables: sysschobjs, syssingleobjrefs, syspalnames.

3
  • 1
    Well that's definitely juicy. Would you mine taking this speculation over to dba.stackexchange.com/q/193574/2639 (my other question) I think you're right and if so this is likely a dupe of that question. I must have skipped the output of these two and just assumed they were same not that they were totally different things. That's really interesting in and of itself. If they're different, there is no substance to this question at all. Dec 20, 2017 at 1:52
  • Yep, I think that's a great idea. When I post a dupe I own it. Let's dupe this and tackle that one thoroughly. I'll play around with it too and we'll see what we can find. I imagine moving forward a lot of people will be wondering what's going on here. Dec 20, 2017 at 2:06
  • 1
    They definitely are different. I just updated my answer here, and I think that it adds weight to the idea that the trailing $ indicates internal system catalog view. I can post an answer on the other one as well. It might be tomorrow, though as I have to call it quits for tonight.. Dec 20, 2017 at 2:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.