Return to Answer

table formatting

Source Link

edited Dec 20, 2017 at 16:52

69.5k
8
155
300

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

name                 source_database   source_schema   source_schema  source_table    source_column  source_column  is_computed_column
----                 ---------------      -------------  ------------      -------------  ------------------
name                 tempdb               sys            sysschobjs        name           0
object_id            tempdb               sys            sysschobjs        id             0
principal_id         tempdb               sys syssingleobjrefs           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           syssingleobjrefs  depid          0
class                tempdb               sys syssingleobjrefs           syssingleobjrefs  class          0
depsubid             tempdb               sys syssingleobjrefs           syssingleobjrefs  depsubid       0
class                mssqlsystemresource  sys            syspalnames       class          0
value                mssqlsystemresource  sys            syspalnames       value          0

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
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

Below is the research output (some of it; not enough space here to put it all in).

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

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

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

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

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

Source Link

created Dec 20, 2017 at 16:46

Solomon Rutzky

69.5k
8
155
300

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:

sysschobjs
syssingleobjrefs
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).

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