2.2.7.3 AllUserData Table
The AllUserData table stores data for all list and document library list items. The table provides a fixed number of generic columns in various data types, affording storage for application-defined variable schemas. A list item can be represented by more than one row in this table, if its list's schema requires more entries of a particular data type than are available in a single row. Application-defined metadata for documents in document libraries also resides in AllUserData, and it is accessed via joins with the Docs View (section 2.2.7.4).
The table is defined using T-SQL syntax, as follows.
-
TABLE AllUserData ( tp_Id int NOT NULL, tp_ListId uniqueidentifier NOT NULL, tp_SiteId uniqueidentifier NOT NULL, tp_RowOrdinal tinyint NOT NULL DEFAULT ((0)), tp_Version int NOT NULL, tp_Author int NULL, tp_Editor int NULL, tp_Modified datetime NULL, tp_Created datetime NULL, tp_Ordering varchar(512) NULL, tp_ThreadIndex varbinary(512) NULL, tp_HasAttachment bit NOT NULL DEFAULT ((0)), tp_ModerationStatus int NOT NULL DEFAULT ((0)), tp_IsCurrent bit NOT NULL DEFAULT ((1)), tp_ItemOrder float NULL, tp_InstanceID int NULL, tp_GUID uniqueidentifier NOT NULL DEFAULT (newid()), tp_CopySource nvarchar(260) NULL DEFAULT (NULL), tp_HasCopyDestinations bit NULL DEFAULT ((0)), tp_AuditFlags int NULL, tp_InheritAuditFlags int NULL, tp_Size int NOT NULL DEFAULT ((0)), tp_WorkflowVersion int NULL, tp_WorkflowInstanceID uniqueidentifier NULL, tp_ParentId uniqueidentifier NOT NULL, tp_DocId uniqueidentifier NOT NULL, tp_DeleteTransactionId varbinary(16) NOT NULL DEFAULT (0x), tp_ContentTypeId varbinary(512) NULL, nvarchar1 nvarchar(255) NULL, nvarchar2 nvarchar(255) NULL, nvarchar3 nvarchar(255) NULL, nvarchar4 nvarchar(255) NULL, nvarchar5 nvarchar(255) NULL, nvarchar6 nvarchar(255) NULL, nvarchar7 nvarchar(255) NULL, nvarchar8 nvarchar(255) NULL, ntext1 nvarchar(max) NULL, ntext2 nvarchar(max) NULL, ntext3 nvarchar(max) NULL, ntext4 nvarchar(max) NULL, sql_variant1 sql_variant NULL, nvarchar9 nvarchar(255) NULL, nvarchar10 nvarchar(255) NULL, nvarchar11 nvarchar(255) NULL, nvarchar12 nvarchar(255) NULL, nvarchar13 nvarchar(255) NULL, nvarchar14 nvarchar(255) NULL, nvarchar15 nvarchar(255) NULL, nvarchar16 nvarchar(255) NULL, ntext5 nvarchar(max) NULL, ntext6 nvarchar(max) NULL, ntext7 nvarchar(max) NULL, ntext8 nvarchar(max) NULL, sql_variant2 sql_variant NULL, nvarchar17 nvarchar(255) NULL, nvarchar18 nvarchar(255) NULL, nvarchar19 nvarchar(255) NULL, nvarchar20 nvarchar(255) NULL, nvarchar21 nvarchar(255) NULL, nvarchar22 nvarchar(255) NULL, nvarchar23 nvarchar(255) NULL, nvarchar24 nvarchar(255) NULL, ntext9 nvarchar(max) NULL, ntext10 nvarchar(max) NULL, ntext11 nvarchar(max) NULL, ntext12 nvarchar(max) NULL, sql_variant3 sql_variant NULL, nvarchar25 nvarchar(255) NULL, nvarchar26 nvarchar(255) NULL, nvarchar27 nvarchar(255) NULL, nvarchar28 nvarchar(255) NULL, nvarchar29 nvarchar(255) NULL, nvarchar30 nvarchar(255) NULL, nvarchar31 nvarchar(255) NULL, nvarchar32 nvarchar(255) NULL, ntext13 nvarchar(max) NULL, ntext14 nvarchar(max) NULL, ntext15 nvarchar(max) NULL, ntext16 nvarchar(max) NULL, sql_variant4 sql_variant NULL, nvarchar33 nvarchar(255) NULL, nvarchar34 nvarchar(255) NULL, nvarchar35 nvarchar(255) NULL, nvarchar36 nvarchar(255) NULL, nvarchar37 nvarchar(255) NULL, nvarchar38 nvarchar(255) NULL, nvarchar39 nvarchar(255) NULL, nvarchar40 nvarchar(255) NULL, ntext17 nvarchar(max) NULL, ntext18 nvarchar(max) NULL, ntext19 nvarchar(max) NULL, ntext20 nvarchar(max) NULL, sql_variant5 sql_variant NULL, nvarchar41 nvarchar(255) NULL, nvarchar42 nvarchar(255) NULL, nvarchar43 nvarchar(255) NULL, nvarchar44 nvarchar(255) NULL, nvarchar45 nvarchar(255) NULL, nvarchar46 nvarchar(255) NULL, nvarchar47 nvarchar(255) NULL, nvarchar48 nvarchar(255) NULL, ntext21 nvarchar(max) NULL, ntext22 nvarchar(max) NULL, ntext23 nvarchar(max) NULL, ntext24 nvarchar(max) NULL, sql_variant6 sql_variant NULL, nvarchar49 nvarchar(255) NULL, nvarchar50 nvarchar(255) NULL, nvarchar51 nvarchar(255) NULL, nvarchar52 nvarchar(255) NULL, nvarchar53 nvarchar(255) NULL, nvarchar54 nvarchar(255) NULL, nvarchar55 nvarchar(255) NULL, nvarchar56 nvarchar(255) NULL, ntext25 nvarchar(max) NULL, ntext26 nvarchar(max) NULL, ntext27 nvarchar(max) NULL, ntext28 nvarchar(max) NULL, sql_variant7 sql_variant NULL, nvarchar57 nvarchar(255) NULL, nvarchar58 nvarchar(255) NULL, nvarchar59 nvarchar(255) NULL, nvarchar60 nvarchar(255) NULL, nvarchar61 nvarchar(255) NULL, nvarchar62 nvarchar(255) NULL, nvarchar63 nvarchar(255) NULL, nvarchar64 nvarchar(255) NULL, ntext29 nvarchar(max) NULL, ntext30 nvarchar(max) NULL, ntext31 nvarchar(max) NULL, ntext32 nvarchar(max) NULL, sql_variant8 sql_variant NULL, int1 int NULL, int2 int NULL, int3 int NULL, int4 int NULL, int5 int NULL, int6 int NULL, int7 int NULL, int8 int NULL, int9 int NULL, int10 int NULL, int11 int NULL, int12 int NULL, int13 int NULL, int14 int NULL, int15 int NULL, int16 int NULL, float1 float NULL, float2 float NULL, float3 float NULL, float4 float NULL, float5 float NULL, float6 float NULL, float7 float NULL, float8 float NULL, float9 float NULL, float10 float NULL, float11 float NULL, float12 float NULL, datetime1 datetime NULL, datetime2 datetime NULL, datetime3 datetime NULL, datetime4 datetime NULL, datetime5 datetime NULL, datetime6 datetime NULL, datetime7 datetime NULL, datetime8 datetime NULL, bit1 bit NULL, bit2 bit NULL, bit3 bit NULL, bit4 bit NULL, bit5 bit NULL, bit6 bit NULL, bit7 bit NULL, bit8 bit NULL, bit9 bit NULL, bit10 bit NULL, bit11 bit NULL, bit12 bit NULL, bit13 bit NULL, bit14 bit NULL, bit15 bit NULL, bit16 bit NULL, uniqueidentifier1 uniqueidentifier NULL, tp_Level tinyint NOT NULL DEFAULT ((1)), tp_IsCurrentVersion bit NOT NULL DEFAULT (CONVERT ([bit],(1),0)), tp_UIVersion int NOT NULL CONSTRAINT [AllUserData_DEFAULT_UIVersionDEFAULT((512)), tp_CalculatedVersion int NOT NULL CONSTRAINT [AllUserData_DEFAULT_CalculatedVersionDEFAULT((0)), tp_UIVersionString AS ((CONVERT([nvarchar],[tp_UIVersion]/(512),0)+'.') + CONVERT([nvarchar],[tp_UIVersion]%(512),0)), tp_DraftOwnerId int NULL DEFAULT (NULL), tp_CheckoutUserId int NULL DEFAULT (NULL) );
tp_Id: The identifier for the list item, uniquely identifying it within the AllUserData table.
tp_ListId: The list identifier (section 2.2.1.5) of the list or document library containing the list item.
tp_SiteId: The site collection identifier (section 2.2.1.9) of the site collection containing the list item.
tp_RowOrdinal: The zero-based ordinal index of this row in the set of rows representing the list item. Additional rows are used to represent list items that have more application-defined columns of one or more data types than can fit in a single row in the AllUserData table.
tp_Version: A counter incremented any time a change is made to the list item, used for internal conflict detection. Due to the mapping of application properties to the generic columns schema in this table, changes to application schema as well as property values can affect a version increment.
tp_Author: The user identifier (section 2.2.1.13) for the user who created the list item.
tp_Editor: The user identifier for the user who last edited the list item.
tp_Modified: A date and time value in UTC format specifying when this list item was last modified.
tp_Created: A date and time value in UTC format specifying when this list item was created.
tp_Ordering: A concatenation of time stamp values in yyyyMMddHHmmss format, specifying the threading structure of the list items in a legacy discussion board list (a list with a List Base type (section 2.2.3.11) of 3). For list items in all other types of lists, this parameter MUST be NULL.
tp_ThreadIndex: A binary structure specifying the list item's position within a legacy discussion board list (a list with a List Base type of 3). For list items in all other types of list, this parameter MUST be NULL.
tp_HasAttachment: A bit set to 1 if the list item has an attachment associated with it; otherwise, it is set to 0.
tp_ModerationStatus: A Moderation Status (section 2.2.3.13) value indicating the current moderation approval status of the list item.
tp_IsCurrent: A bit set to 1 if this is a current version of this list item; otherwise, it is set to 0.
tp_ItemOrder: A value used to calculate the relative order in which to view the list item when displayed with other list items from the same list.
tp_InstanceID: If this list item is associated with a particular instance of a recurring meeting, this is the integer ID of that instance. For all other list items, this MUST be NULL.
tp_GUID: A value uniquely identifying this list item.
tp_CopySource: The URL used as a source for the list item. If this list item was not copied from a source list item, this value MUST be NULL.
tp_HasCopyDestinations: This bit is set to 1 if destination locations for the list item to be copied to have been set. If the list item does not have a destination location set, this value MUST be 0.
tp_AuditFlags: An Audit Flags (section 2.2.2.1) value determining the operations to be tracked on this list item.
tp_InheritAuditFlags: An Audit Flags value for the operations to be tracked on this list item, as determined from parent container Audit Flags settings.
tp_Size: The sum of the size, in bytes, of the content of application-schema columns in the list item. This does not include the size of the stream for list items that have an associated stream.
tp_WorkflowVersion: If the list item is part of a workflow, this stores an integer denoting the state of this list item within that workflow. Otherwise, this value MUST be NULL.
tp_WorkflowInstanceID: A workflow identifier (section 2.2.1.16) value for the currently active workflow instance on this list item. If the list item is not part of a workflow, this value MUST be NULL.
tp_ParentId : The document identifier (section 2.2.1.2) of the item's parent container. For example, the document identifier of the folder or document library containing this subfolder or item. This value MUST NOT be NULL, because every item but one has a parent container. A special empty document identifier, "00000000-0000-0000-0000-000000000000", marks the parent container of the topmost item (the root site) in the site collection.
tp_DocId: The document identifier of the list item.
tp_DeleteTransactionId: An identifier for use with an implementation-specific deleted items recycle bin. This MUST equal 0x if the list item is nondeleted.
tp_ContentTypeId: The binary identifier of the content type associated with the list item.
The next seven columns are duplicated a number of times within the table definition. This is indicated using a suffix "#", which is replaced with a numeral in the column names, described as follows. The number of times each column is duplicated varies and is indicated for each column. Each group of columns is dedicated to hold application-defined fields of a different data type, described as follows.
nvarchar#: Columns for application-defined fields that hold values of type nvarchar. The 64 columns are named nvarchar1 to nvarchar64. If the column does not contain data, this value MUST be NULL.
ntext#: Columns for application-defined fields that hold values of type nvarchar(max). The 32 columns are named ntext1 to ntext32. If the column does not contain data, this value MUST be NULL.
sql_variant#: Columns for application-defined fields that hold values of type sql_variant. The 8 columns are named sql_variant1 to sql_variant8. If the column does not contain data, this value MUST be NULL.
int#: Columns for application-defined fields that hold values of type int. The 16 columns are named int1 to int16. If the column does not contain data, this value MUST be NULL.
float#: Columns for application-defined fields that hold values of type float. The 12 columns are named float1 to float12. If the column does not contain data, this value MUST be NULL.
datetime#: Columns for application-defined fields that hold values of type datetime. The 8 columns are named datetime1 to datetime8. If the column does not contain data, this value MUST be NULL.
bit#: Columns for application-defined fields that hold values of type bit. The 16 columns are named bit1 to bit16. If the column does not contain data, this value MUST be NULL.
uniqueidentifier1: A column for an application-defined field of type uniqueidentifier. If the column does not contain data, this value MUST be NULL.
tp_Level: A publishing level value specifying the publishing status of this version of the list item.
tp_IsCurrentVersion: A bit indicating whether this row corresponds to a current version or an historical version of the list item. This value MUST be 1 if this row contains a current version. Otherwise, it MUST be 0.
tp_UIVersion: The UI version number associated with this list item. The default value of tp_UIVersion is 512, which corresponds to a displayed version of 1.0.
tp_CalculatedVersion: This contains the UI version number if this is an historical version of the list item. This MUST be 0 if the list item is a current version.
tp_UIVersionString: A calculated column containing the value of the tp_UIVersion column as a displayed version string.
tp_DraftOwnerId: The identifier of the user who created this list item as a draft. This value is non-NULL only if the list item exists and is a draft version.
tp_CheckoutUserId: The identifier of the user who checked out this list item. This value is non-NULL only if the list item exists and is checked out.