Table of Contents

Class ObjectSchema.InternalTable

Namespace
LinqToDB.Tools.DataProvider.SqlServer.Schemas
Assembly
linq2db.Tools.dll

sys.internal_tables (Transact-SQL)

Applies to: √ SQL Server

Returns one row for each object that is an internal table. Internal tables are automatically generated by SQL Server to support various features. For example, when you create a primary XML index, SQL Server automatically creates an internal table to persist the shredded XML document data. Internal tables appear in the sys schema of every database and have unique, system-generated names that indicate their function, for example, xml_index_nodes_2021582240_32001 or queue_messages_1977058079
Internal tables do not contain user-accessible data, and their schema are fixed and unalterable. You cannot reference internal table names in Transact-SQL statements. For example, you cannot execute a statement such as SELECT \* FROM *\<sys.internal_table_name>*. However, you can query catalog views to see the metadata of internal tables.


See sys.internal_tables.

[Table(Schema = "sys", Name = "internal_tables", IsView = true)]
public class ObjectSchema.InternalTable
Inheritance
ObjectSchema.InternalTable
Inherited Members
Extension Methods

Properties

CreateDate

create_date NOT NULL datetime

Date the object was created. 
[Column("create_date")]
[NotNull]
public DateTime CreateDate { get; set; }

Property Value

DateTime

FilestreamDataSpaceID

filestream_data_space_id NULL int

Reserved for future use. 
[Column("filestream_data_space_id")]
[Nullable]
public int? FilestreamDataSpaceID { get; set; }

Property Value

int?

InternalType

internal_type NULL tinyint

Type of the internal table:

3 = query_disk_store_query_hints

4 = query_disk_store_query_template_parameterization

6 = query_disk_store_wait_stats

201 = queue_messages

202 = xml_index_nodes

203 = fulltext_catalog_freelist

205 = query_notification

206 = service_broker_map

207 = extended_indexes (such as a spatial index)

208 = filestream_tombstone

209 = change_tracking

210 = tracked_committed_transactions

220 = contained_features

225 = filetable_updates

236 = selective_xml_index_node_table

240 = query_disk_store_query_text

241 = query_disk_store_query

242 = query_disk_store_plan

243 = query_disk_store_runtime_stats

244 = query_disk_store_runtime_stats_interval

245 = query_context_settings 
[Column("internal_type")]
[Nullable]
public byte? InternalType { get; set; }

Property Value

byte?

InternalTypeDesc

internal_type_desc NULL nvarchar(60)

Description of the type of internal table:

QUERY_DISK_STORE_QUERY_HINTS

QUERY_DISK_STORE_QUERY_TEMPLATE_PARAMETERIZATION

QUERY_DISK_STORE_WAIT_STATS

QUEUE_MESSAGES

XML_INDEX_NODES

FULLTEXT_CATALOG_FREELIST

FULLTEXT_CATALOG_MAP

QUERY_NOTIFICATION

SERVICE_BROKER_MAP

EXTENDED_INDEXES

FILESTREAM_TOMBSTONE

CHANGE_TRACKING

TRACKED_COMMITTED_TRANSACTIONS

CONTAINED_FEATURES

FILETABLE_UPDATES

SELECTIVE_XML_INDEX_NODE_TABLE

QUERY_DISK_STORE_QUERY_TEXT

QUERY_DISK_STORE_QUERY

QUERY_DISK_STORE_PLAN

QUERY_DISK_STORE_RUNTIME_STATS

QUERY_DISK_STORE_RUNTIME_STATS_INTERVAL

QUERY_CONTEXT_SETTINGS 
[Column("internal_type_desc")]
[Nullable]
public string? InternalTypeDesc { get; set; }

Property Value

string

IsMSShipped

is_ms_shipped NULL bit

Object is created by an internal SQL Server component. 
[Column("is_ms_shipped")]
[Nullable]
public bool? IsMSShipped { get; set; }

Property Value

bool?

IsPublished

is_published NULL bit

Object is published. 
[Column("is_published")]
[Nullable]
public bool? IsPublished { get; set; }

Property Value

bool?

IsSchemaPublished

is_schema_published NULL bit

Only the schema of the object is published. 
[Column("is_schema_published")]
[Nullable]
public bool? IsSchemaPublished { get; set; }

Property Value

bool?

LobDataSpaceID

lob_data_space_id NOT NULL int

Non-zero value is the ID of data space (filegroup or partition-scheme) that holds the large object (LOB) data for this table. 
[Column("lob_data_space_id")]
[NotNull]
public int LobDataSpaceID { get; set; }

Property Value

int

ModifyDate

modify_date NOT NULL datetime

Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when an index on the table or view is created or altered. 
[Column("modify_date")]
[NotNull]
public DateTime ModifyDate { get; set; }

Property Value

DateTime

Name

name NOT NULL sysname

Object name. 
[Column("name")]
[NotNull]
public string Name { get; set; }

Property Value

string

Object

objects (sys.objects)

[Association(ThisKey = "ObjectID", OtherKey = "ObjectID", CanBeNull = false)]
public ObjectSchema.Object Object { get; set; }

Property Value

ObjectSchema.Object

ObjectID

object_id NOT NULL int

Object identification number. Is unique within a database. 
[Column("object_id")]
[NotNull]
public int ObjectID { get; set; }

Property Value

int

ParentID

parent_id NULL int

ID of the parent, regardless of whether it is schema-scoped or not. Otherwise, 0 if there is no parent.

queue_messages = object_id of queue

xml_index_nodes = object_id of the xml index

fulltext_catalog_freelist = fulltext_catalog_id of the full-text catalog

fulltext_index_map = object_id of the full-text index

query_notification, or service_broker_map = 0

extended_indexes = object_id of an extended index, such as a spatial index

object_id of the table for which table tracking is enabled = change_tracking 
[Column("parent_id")]
[Nullable]
public int? ParentID { get; set; }

Property Value

int?

ParentMinorID

parent_minor_id NULL int

Minor ID of the parent.

xml_index_nodes = index_id of the XML index

extended_indexes = index_id of an extended index, such as a spatial index

0 = queue_messages, fulltext_catalog_freelist, fulltext_index_map, query_notification, service_broker_map, or change_tracking 
[Column("parent_minor_id")]
[Nullable]
public int? ParentMinorID { get; set; }

Property Value

int?

ParentObjectID

parent_object_id NOT NULL int

ID of the object to which this object belongs.

0 = Not a child object. 
[Column("parent_object_id")]
[NotNull]
public int ParentObjectID { get; set; }

Property Value

int

PrincipalID

principal_id NULL int

ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership.

NULL if there's no alternate individual owner.

Is NULL if the object type is one of the following values:

C = Check constraint
D = Default (constraint or stand-alone)
F = Foreign key constraint
PK = Primary key constraint
R = Rule (old-style, stand-alone)
TA = Assembly (CLR-integration) trigger
TR = SQL trigger
UQ = Unique constraint
EC = Edge constraint 
[Column("principal_id")]
[Nullable]
public int? PrincipalID { get; set; }

Property Value

int?

SchemaID

schema_id NOT NULL int

ID of the schema that the object is contained in.

Schema-scoped system objects are always contained in the sys or INFORMATION_SCHEMA schemas. 
[Column("schema_id")]
[NotNull]
public int SchemaID { get; set; }

Property Value

int

Type

type NOT NULL char(2)

Object type:

AF = Aggregate function (CLR)
C = Check constraint
D = Default (constraint or stand-alone)
F = Foreign key constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function (TVF)
IT = Internal table
P = SQL stored procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = Primary key constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
U = Table (user-defined)
V = View

Applies to: SQL Server 2012 (11.x) and later versions

SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function (TVF)
TR = SQL DML trigger
TT = Table type
UQ = unique constraint
X = Extended stored procedure

Applies to: SQL Server 2014 (12.x) and later versions, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (PDW)

ST = Statistics tree

Applies to: SQL Server 2016 (13.x) and later versions, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (PDW)

ET = External table

Applies to: SQL Server 2017 (14.x) and later versions, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (PDW)

EC = Edge constraint 
[Column("type")]
[NotNull]
public string Type { get; set; }

Property Value

string

TypeDesc

type_desc NULL nvarchar(60)

Description of the object type:

AGGREGATE_FUNCTION
CHECK_CONSTRAINT
CLR_SCALAR_FUNCTION
CLR_STORED_PROCEDURE
CLR_TABLE_VALUED_FUNCTION
CLR_TRIGGER
DEFAULT_CONSTRAINT
EDGE_CONSTRAINT
EXTENDED_STORED_PROCEDURE
FOREIGN_KEY_CONSTRAINT
INTERNAL_TABLE
PLAN_GUIDE
PRIMARY_KEY_CONSTRAINT
REPLICATION_FILTER_PROCEDURE
RULE
SEQUENCE_OBJECT
SERVICE_QUEUE
SQL_INLINE_TABLE_VALUED_FUNCTION
SQL_SCALAR_FUNCTION
SQL_STORED_PROCEDURE
SQL_TABLE_VALUED_FUNCTION
SQL_TRIGGER
SYNONYM
SYSTEM_TABLE
TYPE_TABLE
UNIQUE_CONSTRAINT
USER_TABLE
VIEW 
[Column("type_desc")]
[Nullable]
public string? TypeDesc { get; set; }

Property Value

string