As of May 31, 2023, we have updated our Code of Conduct.
Meta Stack Exchange

Return to Revisions

77 of 113
added posthistory back in its old format as I don't fancy digging into XLSM for now.
rene
rene
  • 85.5k
  • 16
  • 230
  • 476

About this list:

● Hover over blue fields for more info and notes
   ⓘ   =  Reference table (hover for list of values)
   ∅   =  Optional field (Nullable)
    ⋈    =  Joined (on foreign key)
● Tables are mostly listed alphabetically (common at top, reference at bottom)

∴ View 'Show All Types' Query
∴ View 'Table Schema' Query

🔗 Jump to: Stack Exchange Data Explorer (Help)
🔗 Jump to: Stack Exchange Data Dump (Readme)
🔗 Jump to: Stack Exchange API (Support)

Quick Reference Charts: (PNG images to bookmark/download/print)
   Image: SEDE Table List   Image: SEDE Reference Table Values

STACK EXCHANGE DATA EXPLORER: TABLES

Posts (and PostsWithDeleted)
●**Id** int (10)
●PostTypeIdⓘ tinyint (3)
●AcceptedAnswerId int (10)
●ParentId int (10)
●**CreationDate** datetime
●DeletionDate datetime
●**Score** int (10)
●ViewCount int (10)
●Body nvarchar (-1)
●OwnerUserId int (10)
●OwnerDisplayName nvarchar (40)
●LastEditorUserId int (10)
●LastEditorDisplayName nvarchar (40)
●LastEditDate datetime
●LastActivityDate datetime
●Title nvarchar (250)
●Tags nvarchar (250)
●AnswerCount int (10)
●**CommentCount** int (10)
●FavoriteCount int (10)
●ClosedDate datetime
●CommunityOwnedDate datetime

Users
●**Id** int (10)
●Reputation int (10)
●**CreationDate** datetime
●**DisplayName** nvarchar (40)
●LastAccessDate datetime
●WebsiteUrl nvarchar (200)
●Location nvarchar (100)
●AboutMe nvarchar (-1)
●**Views** int (10)
●UpVotes int (10)
●**DownVotes** int (10)
●ProfileImageUrl nvarchar (200)
●EmailHash varchar (32)
●Age int (10)
●AccountId int (10)

Comments
●**Id** int (10)
●PostId int (10)
●**Score** int (10)
●Text nvarchar (600)
●**CreationDate** datetime
●UserDisplayName nvarchar (30)
●UserId int (10)

Badges
●**Id** int (10)
●UserId int (10)
●**Name** nvarchar (50)
●**Date** datetime
●Class tinyint (3)
●TagBased bit

CloseAsOffTopicReasonTypes
●**Id** smallint (5)
●**IsUniversal** bit
●MarkdownMini nvarchar (500)
●**CreationDate** datetime
●CreationModeratorId int (10)
●**ApprovalDate** datetime
●ApprovalModeratorId int (10)
●DeactivationDate datetime
●DeactivationModeratorId int (10)

PendingFlags
●**Id** int (10)
●FlagTypeIdⓘ tinyint (3)
●PostId int (10)
●**CreationDate** date
●CloseReasonTypeIdⓘ tinyint (3)
●CloseAsOffTopicReasonTypeId smallint (5)
●DuplicateOfQuestionId int (10)
●BelongsOnBaseHostAddress nvarchar (100)

PostFeedback
●**Id** int (10)
●PostId int (10)
●**IsAnonymous** bit
●VoteTypeIdⓘ tinyint (3)
●CreationDate datetime

PostHistory

  • Id
  • PostHistoryTypeId (listed in the PostHistoryTypes table)
    1 = Initial Title - initial title (questions only)
    2 = Initial Body - initial post raw body text
    3 = Initial Tags - initial list of tags (questions only)
    4 = Edit Title - modified title (questions only)
    5 = Edit Body - modified post body (raw markdown)
    6 = Edit Tags - modified list of tags (questions only)
    7 = Rollback Title - reverted title (questions only)
    8 = Rollback Body - reverted body (raw markdown)
    9 = Rollback Tags - reverted list of tags (questions only)
    10 = Post Closed - post voted to be closed
    11 = Post Reopened - post voted to be reopened
    12 = Post Deleted - post voted to be removed
    13 = Post Undeleted - post voted to be restored
    14 = Post Locked - post locked by moderator
    15 = Post Unlocked - post unlocked by moderator
    16 = Community Owned - post now community owned
    17 = Post Migrated - post migrated - now replaced by 35/36 (away/here)
    18 = Question Merged - question merged with deleted question
    19 = Question Protected - question was protected by a moderator.
    20 = Question Unprotected - question was unprotected by a moderator.
    21 = Post Disassociated - OwnerUserId removed from post by admin
    22 = Question Unmerged - answers/votes restored to previously merged question
    24 = Suggested Edit Applied
    25 = Post Tweeted
    31 = Comment discussion moved to chat
    33 = Post notice added - comment contains foreign key to PostNotices
    34 = Post notice removed - comment contains foreign key to PostNotices
    35 = Post migrated away - replaces id 17
    36 = Post migrated here - replaces id 17
    37 = Post merge source
    38 = Post merge destination

Additionally, in older dumps (all guesses, all seem no longer present in the wild):
23 = Unknown dev related event
26 = Vote nullification by dev (ERM?)
27 = Post unmigrated/hidden moderator migration?
28 = Unknown suggestion event
29 = Unknown moderator event (possibly de-wikification?)
30 = Unknown event (too rare to guess)

  • PostId
  • RevisionGUID: At times more than one type of history record can be recorded by a single action. All of these will be grouped using the same RevisionGUID
  • CreationDate (e.g. 2009-03-05T22:28:34.823)
  • UserId
  • UserDisplayName: populated if a user has been removed and no longer referenced by user Id
  • Comment: This field will contain the comment made by the user who edited a post.
  • If PostHistoryTypeId = 10, this field contains the CloseReasonId of the close reason (listed in CloseReasonTypes):
    Old close reasons:
    1 = Exact Duplicate
    2 = Off-topic
    3 = Subjective and argumentative
    4 = Not a real question
    7 = Too localized
    10 = General reference
    20 = Noise or pointless (Meta sites only)
    Current close reasons:
    101 = Duplicate
    102 = Off-topic
    103 = Unclear what you're asking
    104 = Too broad
    105 = Primarily opinion-based

- If PostHistoryTypeId in (33,34) this field contains the PostNoticeId of the PostNotice

  • Text: A raw version of the new value for a given revision
    - If PostHistoryTypeId in (10,11,12,13,14,15,19,20,35) this column will contain a JSON encoded string with all users who have voted for the PostHistoryTypeId
    - If it is a duplicate close vote, the JSON string will contain an array of original questions as OriginalQuestionIds
    - If PostHistoryTypeId = 17 this column will contain migration details of either from <url> or to <url>

PostLinks
●**Id** int (10)
●**CreationDate** datetime
●PostId int (10)
●RelatedPostId int (10)
●LinkTypeId tinyint (3)

PostNotices
●**Id** int (10)
●PostId int (10)
●PostNoticeTypeIdⓘ int (10)
●CreationDate datetime
●DeletionDate datetime
●ExpiryDate datetime
●Body nvarchar (-1)
●OwnerUserId int (10)
●DeletionUserId int (10)

PostTags
●PostId int (10)
●TagId int (10)

ReviewTaskResults
●**Id** int (10)
●ReviewTaskId int (10)
●ReviewTaskResultTypeIdⓘ tinyint (3)
●CreationDate date
●RejectionReasonIdⓘ tinyint (3)
●Comment nvarchar (150)

ReviewTasks
●**Id** int (10)
●ReviewTaskTypeIdⓘ tinyint (3)
●CreationDate date
●DeletionDate date
●ReviewTaskStateIdⓘ tinyint (3)
●PostId int (10)
●SuggestedEditId int (10)
●CompletedByReviewTaskId int (10)

SuggestedEdits
●**Id** int (10)
●PostId int (10)
●**CreationDate** datetime
●ApprovalDate datetime
●RejectionDate datetime
●OwnerUserId int (10)
●Comment nvarchar (800)
●Text nvarchar (-1)
●Title nvarchar (250)
●Tags nvarchar (250)
●RevisionGUID uniqueidentifier

SuggestedEditVotes
●**Id** int (10)
●SuggestedEditId int (10)
●UserId int (10)
●VoteTypeIdⓘ tinyint (3)
●CreationDate datetime
●TargetUserId int (10)
●**TargetRepChange** int (10)

Tags
●**Id** int (10)
●**TagName** nvarchar (35)
●**Count** int (10)
●ExcerptPostId int (10)
●WikiPostId int (10)

TagSynonyms
●**Id** int (10)
●**SourceTagName** nvarchar (35)
●**TargetTagName** nvarchar (35)
●**CreationDate** datetime
●OwnerUserId int (10)
●**AutoRenameCount** int (10)
●LastAutoRename datetime
●**Score** int (10)
●ApprovedByUserId int (10)
●ApprovalDate datetime

Votes
●**Id** int (10)
●PostId int (10)
●VoteTypeIdⓘ tinyint (3)
●UserId int (10)
●CreationDate datetime
●BountyAmount int (10)

STACK EXCHANGE DATA EXPLORER: REFERENCE TABLES

ⓘ CloseReasonTypes
●**Id** tinyint (3)
●**Name** nvarchar (200)
●Description nvarchar (500)

ⓘ FlagTypes
●**Id** tinyint (3)
●**Name** nvarchar (50)
●**Description** nvarchar (500)

ⓘ PostHistoryTypes
●**Id** tinyint (3)
●**Name** nvarchar (50)

ⓘ PostNoticeTypes
●**Id** int (10)
●ClassId tinyint (3)
●**Name** nvarchar (80)
●Body nvarchar (-1)
●**IsHidden** bit
●**Predefined** bit
●PostNoticeDurationId int (10)

ⓘ PostTypes
●**Id** tinyint (3)
●**Name** nvarchar (50)

ⓘ ReviewRejectionReasons
●**Id** tinyint (3)
●**Name** nvarchar (100)
●**Description** nvarchar (300)
●**PostTypeId**ⓘ tinyint (3)

ⓘ ReviewTaskResultTypes
●**Id** tinyint (3)
●**Name** nvarchar (100)
●**Description** nvarchar (300)

ⓘ ReviewTaskStates
●**Id** tinyint (3)
●**Name** nvarchar (50)
●**Description** nvarchar (300)

ⓘ ReviewTaskTypes
●**Id** tinyint (3)
●**Name** nvarchar (50)
●**Description** nvarchar (300)

ⓘ VoteTypes
●**Id** tinyint (3)
●**Name** nvarchar (50)

xxxTypes
Not listed here:

  • xxxTypes tables which list (Id, Name) pairs for Posts.PostTypeId, Votes.VoteTypeId, etc.
  • See Show all types for an up-to-date list of all types.

TIMESTAMPS:

  • All timestamps are UTC, default format: yyyy-MM-dd hh:mm:ss (stored with milliseconds).
  • Example of conversion current time to PST (including DST) using At Time Zone:
  • SELECT GetDate() At Time Zone 'UTC' At Time Zone 'Pacific Standard Time'
  • To list time zones: SELECT * FROM sys.time_zone_info
Stu Thompson
  • 12.1k
  • 6
  • 36
  • 66

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.