What is the significance of the trailing dollar sign `$` on identifiers in SQL Server?

Question

Dumping some internal views, such as sys.system_objects as done in this question I see that some identifiers end with a dollar-sign $

CREATE VIEW sys.system_objects AS
    -- cutout
    FROM sys.sysschobjs$ o -- XXX: HERE
    LEFT JOIN sys.syspalnames n
    -- not relevant

And, this works too

SELECT * FROM sys.sysschobjs$;

Even if only by convention, what is the rule here regarding this? Should I be using this in my own code?

Searching I see a lot of stuff about Excel (which is unrelated), and Money literals (which is also unrelated).

I posted another question which I thought was unrelated, but I believe this terminal $ will shed a lot more light on it. Feel free to check that question for an example of some confusion

Answer 1

Even if only by convention, what is the rule here regarding this?

There is nothing stopping you from using a $ in your code... generally, though, using reserved values is frowned upon. Most internal items you'll see will use a $, a % or %%, # or ##. I'd stay away from using all of those in my user code.

Should I be using this in my own code?

See above.

Dumping some internal views [...]

There are various other items you'll see such as OPENROWSET or OPENTABLE. I'll save you from another question, no there isn't a way to see that definition.

You can think of Dynamic Management Views (DMVs) as taking a memory structure and making it a rowset... and literally that's what is being done. There is a difference between an internal base table (which holds metadata about the database objects) and DMVs (which are in memory structures).

DMVs will be helpful to you, internal base tables will not be very helpful as you won't find much (if any) documentation on the table itself or the values inside of it.

Can you describe the convention amongst $, a % or %%, # or ## that is used internally?

No, I would also not worry about it as it makes no actual difference to you.

When you say "no there isn't a way to see that definition" you mean the definition for sys.sysschobjs$

Depends, do you see OPENTABLE or OPENROWSET in that definition as I stated above? If not, then you can probably see the base tables over a DAC or DAC + Single User Mode connection.

What would you call sysschobjs$ you say "internal item" is that the most specific name in the Microsoft parlance?

I would call it an internal base metadata table. You could call it whatever you like, just how "automobile" is also called a "car".

You described DMVs (which I believe I understand), and Base Tables which I also believe I understand, but not internal items that end in $ or how they relate DMVs and Base Tables.

Some will end in $ some won't, some will have #, some won't. Like I said above, you'll find a variety of items used. There isn't a hard and fast rule. Example being you see that table end in $ but there are functions that also use $. Again, at the end of the day it's a name and nothing else.

I believe you're reading into this waaaaaaaaay too much. There is no secret code or handshake or society.

Answer 2

It appears that the trailing $ for object references in publicly accessible system catalog views indicates an internal system catalog view. I say this because when looking at the source column for sys.objects, the source is not sys.objects$ but instead is a combination of the following three internal tables:

1. sysschobjs
2. syssingleobjrefs
3. syspalnames

When looking at the source columns for sys.objects$, they are the same sources as noted for sys.objects for the same columns, but there are plenty more columns in sys.objects$ (51 compared to 17 for sys.objects). Then, looking at the source columns for one of the base objects, syspalnames, the sources were finally that same object: sys.syspalnames.

While I can get the definition of sys.objects, I cannot get the definition of sys.objects$.

This pattern holds true for sys.configurations as well. Its definition points to sys.configurations$, yet the columns are all either expressions or have an actual source of sys.sysobjvalues. And the source for all columns in sys.sysobjvalues is itself: sys.sysobjvalues.

Below is the research output (some of it; not enough space here to put it all in), done on SQL Server 2012.

I used the following query via a DAC connection. The final input parameter value of 1 is import as it enables the reporting of the "source" info.

USE [tempdb];

SELECT [name], [source_database], [source_schema], [source_table], [source_column],
       [is_computed_column]
FROM   sys.dm_exec_describe_first_result_set(N'SELECT * FROM sys.objects$', NULL, 1)
ORDER BY [column_ordinal] ASC;

The command-line was:

sqlcmd -A -E -Q "USE [tempdb]; SELECT [name], [source_database], [source_schema], [source_table], [source_column], [is_computed_column] FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM sys.objects$', NULL, 1) ORDER BY [column_ordinal] ASC;" -o InternalViews_SysObjects$.txt -W

---

sys.objects

name                 source_database      source_schema  source_table      source_column  is_computed_column
----                 ---------------      -------------  ------------      -------------  ------------------
name                 tempdb               sys            sysschobjs        name           0
object_id            tempdb               sys            sysschobjs        id             0
principal_id         tempdb               sys            syssingleobjrefs  indepid        0
schema_id            tempdb               sys            sysschobjs        nsid           0
parent_object_id     tempdb               sys            sysschobjs        pid            0
type                 NULL                 NULL           NULL              NULL           1
type_desc            mssqlsystemresource  sys            syspalnames       name           0
create_date          tempdb               sys            sysschobjs        created        0
modify_date          tempdb               sys            sysschobjs        modified       0
is_ms_shipped        NULL                 NULL           NULL              NULL           1
is_published         NULL                 NULL           NULL              NULL           1
is_schema_published  NULL                 NULL           NULL              NULL           1
depid                tempdb               sys            syssingleobjrefs  depid          0
class                tempdb               sys            syssingleobjrefs  class          0
depsubid             tempdb               sys            syssingleobjrefs  depsubid       0
class                mssqlsystemresource  sys            syspalnames       class          0
value                mssqlsystemresource  sys            syspalnames       value          0

sys.objects$

name                                source_database      source_schema  source_table      source_column  is_computed_column
----                                ---------------      -------------  ------------      -------------  ------------------
name                                tempdb               sys            sysschobjs        name           0
object_id                           tempdb               sys            sysschobjs        id             0
principal_id                        tempdb               sys            syssingleobjrefs  indepid        0
schema_id                           tempdb               sys            sysschobjs        nsid           0
parent_object_id                    tempdb               sys            sysschobjs        pid            0
type                                tempdb               sys            sysschobjs        type           0
type_desc                           mssqlsystemresource  sys            syspalnames       name           0
property                            tempdb               sys            sysschobjs        intprop        0
create_date                         tempdb               sys            sysschobjs        created        0
modify_date                         tempdb               sys            sysschobjs        modified       0
is_ms_shipped                       NULL                 NULL           NULL              NULL           1
is_auto_dropped                     NULL                 NULL           NULL              NULL           1
is_system_named                     NULL                 NULL           NULL              NULL           1
is_published                        NULL                 NULL           NULL              NULL           1
is_schema_published                 NULL                 NULL           NULL              NULL           1
is_cycling                          NULL                 NULL           NULL              NULL           1
lock_on_bulk_load                   NULL                 NULL           NULL              NULL           1
is_disabled                         NULL                 NULL           NULL              NULL           1
is_auto_executed                    NULL                 NULL           NULL              NULL           1
is_activation_enabled               NULL                 NULL           NULL              NULL           1
has_opaque_metadata                 NULL                 NULL           NULL              NULL           1
is_not_for_replication              NULL                 NULL           NULL              NULL           1
is_receive_enabled                  NULL                 NULL           NULL              NULL           1
is_cached                           NULL                 NULL           NULL              NULL           1
is_not_trusted                      NULL                 NULL           NULL              NULL           1
is_enqueue_enabled                  NULL                 NULL           NULL              NULL           1
with_check_option                   NULL                 NULL           NULL              NULL           1
is_retention_enabled                NULL                 NULL           NULL              NULL           1
has_unchecked_assembly_data         NULL                 NULL           NULL              NULL           1
update_referential_action           NULL                 NULL           NULL              NULL           1
delete_referential_action           NULL                 NULL           NULL              NULL           1
is_replicated                       NULL                 NULL           NULL              NULL           1
is_execution_replicated             NULL                 NULL           NULL              NULL           1
has_replication_filter              NULL                 NULL           NULL              NULL           1
is_repl_serializable_only           NULL                 NULL           NULL              NULL           1
is_merge_published                  NULL                 NULL           NULL              NULL           1
skips_repl_constraints              NULL                 NULL           NULL              NULL           1
is_sync_tran_subscribed             NULL                 NULL           NULL              NULL           1
uses_ansi_nulls                     NULL                 NULL           NULL              NULL           1
null_on_null_input                  NULL                 NULL           NULL              NULL           1
uses_database_collation             NULL                 NULL           NULL              NULL           1
is_tracked_by_cdc                   NULL                 NULL           NULL              NULL           1
large_value_types_out_of_row        NULL                 NULL           NULL              NULL           1
lock_escalation_option              NULL                 NULL           NULL              NULL           1
is_poison_message_handling_enabled  NULL                 NULL           NULL              NULL           1
is_filetable                        NULL                 NULL           NULL              NULL           1
depid                               tempdb               sys            syssingleobjrefs  depid          0
class                               tempdb               sys            syssingleobjrefs  class          0
depsubid                            tempdb               sys            syssingleobjrefs  depsubid       0
class                               mssqlsystemresource  sys            syspalnames       class          0
value                               mssqlsystemresource  sys            syspalnames       value          0

sys.syspalnames

name   source_database      source_schema  source_table  source_column  is_computed_column
----   ---------------      -------------  ------------  -------------  ------------------
class  mssqlsystemresource  sys            syspalnames   class          0
value  mssqlsystemresource  sys            syspalnames   value          0
name   mssqlsystemresource  sys            syspalnames   name           0

Answer 3

[Illuminati Views$]

For consistency, and because we need a name I'll call these "Illuminati Views", because why not. :)

The first thing that must be cleared up is everything is an object, including Illuminati Views. All databases have a table, sysschobjs, which contain that database's objects. And all databases pull from the sysschobjs in Resource DB (mssqlsystemresource).

SELECT
  (SELECT count(*) FROM mssqlsystemresource.sys.sysschobjs) AS resource,
  (SELECT count(*) FROM msdb.sys.sysschobjs) AS msdb,
  (SELECT count(*) FROM master.sys.sysschobjs) AS master;

resource    msdb        master     
----------- ----------- -----------
       3665        3635        2435

You can see here some isolation, but it's not purely hierarchical. A lot of things are visible in the Resource DB but are hidden from outside it. Sometimes those things bleed through and are used under the hood elsewhere but not available directly.

From all the objects in sys.sysschobjs across all schemas I created a script and brute-force detected 18 of our Illuminati Views,

[V - -667123516] used to detect -620952411      [sys].[dm_db_xtp_table_memory_stats$]
[V -       -495] used to detect -217345680      [sys].[sql_expression_dependencies$]
[V -       -447] used to detect -270688537      [sys].[conversation_endpoints$]
[V -       -446] used to detect -332991205      [sys].[conversation_groups$]
[V -       -444] used to detect -397721966      [sys].[services$]
[V -       -442] used to detect -123002535      [sys].[service_contracts$]
[V -       -441] used to detect -189344319      [sys].[service_message_types$]
[V -       -418] used to detect -825154186      [sys].[numbered_procedures$]
[V -       -397] used to detect -993951361      [sys].[indexes$]
[V -       -394] used to detect -150            [sys].[system_parameters$]
[V -       -393] used to detect -532309064      [sys].[parameters$]
[V -       -392] used to detect -149            [sys].[system_columns$]
[V -       -385] used to detect -506864465      [sys].[objects$]
[V -       -259] used to detect -463422670      [sys].[server_audits$]
[V -       -225] used to detect -375483549      [sys].[messages$]
[V -       -224] used to detect -441193657      [sys].[configurations$]
[V -       -106] used to detect -151            [sys].[syscolumns$]
[S -         34] used to detect 34              [sys].[sysschobjs$]

Both of the numbers are the sysschobjs.id of the source and the Illuminati View detected. The first letter corresponds to the sysschobjs.type of the sysschobjs that I used to generate the Illuminati View's name from. The base TYPE was of the object used to derive the Illuminati View name. Though I tested all of the objects (including ones not of TYPE IN ('S','V')), only one of them was detected of type='S', SYSTEM_TABLE. All the other types used to find Illuminati Views were mere VIEW.

After that I started playing around with the mssqlsystemresource directly. With this you can just query mssqlsystemresource.sys.sysschobjs. Using that method, I found one more, sysschobjs_dbscoped$ which has no corresponding object. Most Illuminati Views are in fact just lower level sources for Catalog Views by similar names. One exception is sys.sysschobjs$; sysschobjs is not a view, but a SYSTEM_TABLE. And, it may be somewhat confusing to call them lower level because while often Catalog Views pull from them, at the very least they sit above System Tables,

• sys.messages$ for instances reads from sys.sysusermsgs
• sys.parameters$ for instance reads from sys.syscolpars

Out of the 3665 objects in mssqlsystemresource.sys.sysschobjs

• 2422 objects are in msdb.sys.sysschobjs
• 1243 objects are not in msdb.sys.sysschobjs (this includes the Illuminati Views)
• 2402 objects are in master.sys.sysschobjs
• 1263 objects are not in master.sys.sysschobjs

All of this is from SQL Server 2017 on Ubuntu, though most of is not likely to change much

There is a wrapper specifically for sysschobjs in the OrcaMDF project, you can see the use of that here. Apparently, for SQL Server 2008 the MDF exploration needed no other Illuminati Views than sysschobjs, or perhaps the others on this list were not yet available.