0

Please login or register to vote for this query.

(click on this box to dismiss)

Want to archive your contributions in case Stack Exchange goes down? Just enter your SE network account ID below, run the query and download the resulting CSV file. The results include the current body of each post in both Markdown and parsed HTML form, the question title and tags and a bunch of other useful metadata. To assist with CC-By-SA compliance, the results also include the names of all other users who have edited the post or its title (excluding rollbacks). This cross-site query automatically finds all your posts on all sites in the SE network. As such, it can take a while to run. For a quicker single-site version, see https://data.stackexchange.com/meta.stackexchange/query/952859

Q&A for professional and enthusiast programmers

-- AccountId: Your SE network account ID number, found in the URL of your network profile page:

create table #PostsByUser (
  PostId int primary key,
  ParentId int,
  PostTypeId int
)
create table #Results (
  [Site] nvarchar(128) not null,
  [Post ID] int not null,
  [URL] nvarchar(max),
  [Link] nvarchar(max),
  [Type] nvarchar(50),
  [Title] nvarchar(250),
  [HTML] nvarchar(max),
  [Markdown] nvarchar(max),
  [Tags] nvarchar(250),
  [Created] datetime,
  [Last Edit] datetime,
  [Edited By] nvarchar(max),
  [Score] int,
  [Favorites] int,
  [Views] int,
  [Answers] int,
  [Accepted] nvarchar(20),
  [CW] nvarchar(20),
  [Closed] nvarchar(20)
)

declare @accountid as int = ##AccountId:int##

-- Site details, cf. https://meta.stackexchange.com/a/359795/295232
CREATE TABLE #Sites (URL NVARCHAR(100), SiteName NVARCHAR(100), DatabaseName NVARCHAR(50));
-- fetch Markdown for post containing table with site details
DECLARE @md NVARCHAR(MAX);
SELECT TOP 1 @md = Text
  FROM [StackExchange.Meta].dbo.PostHistory
  WHERE PostId = 359796 AND PostHistoryTypeId = 5
  ORDER BY CreationDate DESC;
-- split Markdown on pipe symbol
CREATE TABLE #TableEntries (ID INT IDENTITY(1, 1), Value NVARCHAR(100));
INSERT INTO #TableEntries (Value)
SELECT LEFT(TRIM(VALUE), 100) FROM STRING_SPLIT(@md, '|')
  WHERE LEN(TRIM(REPLACE(REPLACE(VALUE, CHAR(10), ''), CHAR(13), ''))) > 0;
-- 'pivot' - we have to discard the introductory text, headers and horizontal lines
INSERT INTO #Sites
SELECT t0.Value, t1.Value, t2.Value
  FROM #TableEntries AS t0
  INNER JOIN #TableEntries AS t1
    ON t0.Id + 1 = t1.Id
  INNER JOIN #TableEntries AS t2
    ON t0.Id + 2 = t2.Id
  WHERE t0.Id >= 8 -- that's where the real content starts
    AND t0.Id % 3 = 2;

DECLARE sites CURSOR FOR
  SELECT name
  FROM sys.databases
  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange')
    AND name NOT LIKE '%_Temp' -- otherwise it won't work while SEDE refreshes

DECLARE @sitedbname AS nvarchar(128)
DECLARE @query AS nvarchar(max)

OPEN sites
FETCH NEXT FROM sites INTO @sitedbname
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @query = '
    use [' + @sitedbname + '];
    
    declare @localuserid as int = (select Id from Users where AccountId = ' + cast(@accountid as nvarchar(max)) + ');

    insert into #PostsByUser
    select Id as PostId, ParentId, PostTypeId
    from Posts
    where OwnerUserId = @localuserid;

    with
    PostsByUser as (
      select * from #PostsByUser
    ),
    LastRevisions as (
      select
        PostsByUser.PostId as PostId,
        max(PostHistory.Id) as LastRevId
      from
        PostsByUser
        join PostHistory on PostHistory.PostId = PostsByUser.PostId
      where
        PostHistory.PostHistoryTypeId in (2,5,8)  -- create/edit/rollback body
      group by
        PostsByUser.PostId
    ),
    Editors as (
      select
        PostHistory.PostId as PostId,
        PostHistory.UserId as UserId,
        coalesce(Users.DisplayName, 
          PostHistory.UserDisplayName COLLATE SQL_Latin1_General_CP1_CS_AS ) as UserName,
        min(PostHistory.CreationDate) as FirstEditDate
      from
        PostsByUser
        join PostHistory on PostHistory.PostId = PostsByUser.PostId
        left join Users on Users.Id = PostHistory.UserId
      where
        PostHistory.PostHistoryTypeId in (4,5)  -- edit body/title
        and (PostHistory.UserId != @localuserid or PostHistory.UserId is null)
      group by
        PostHistory.PostId,
        PostHistory.UserId,
        coalesce(Users.DisplayName, 
          PostHistory.UserDisplayName COLLATE SQL_Latin1_General_CP1_CS_AS )
    ),
    EditorsGrouped as (
      select
        PostId,
        string_agg(UserName + '' ('' + coalesce(cast(UserId as nvarchar), ''n/a'') + '')'', '', '')
          within group ( order by FirstEditDate asc ) as EditorNames
      from
        Editors
      group by
        PostId
    ),
    WikiTags as (
      select
        PostId, TagName
      from
        PostsByUser
        join Tags on PostId = ExcerptPostId
      where
        PostTypeId = 4  -- tag excerpt
    union
      select
        PostId, TagName
      from
        PostsByUser
        join Tags on PostId = WikiPostId
      where
        PostTypeId = 5  -- tag wiki
    ),
    Dupes as (
      select distinct
        PostsByUser.PostId
      from
        PostsByUser
        join PostLinks on PostLinks.PostId = PostsByUser.PostId
      where
        PostTypeId = 1 and LinkTypeId = 3  -- question, duplicate
    )
    insert into #Results
    select
      #Sites.SiteName as [Site],
      Posts.Id as [Post Id],
      concat(#Sites.URL, ''/q/'', Posts.Id) as [URL],
      concat(#Sites.URL, ''/q/'', Posts.Id, ''|'', coalesce(Posts.Title, Parent.Title)) as [Link],
      PostTypes.Name as [Type],
      coalesce(Posts.Title, Parent.Title) as [Title],
      Posts.Body as [HTML],
      PostHistory.Text as [Markdown],
      coalesce(WikiTags.TagName, 
         Posts.Tags COLLATE SQL_Latin1_General_CP1_CS_AS , 
        Parent.Tags COLLATE SQL_Latin1_General_CP1_CS_AS ) as [Tags],
      Posts.CreationDate as [Created],
      Posts.LastEditDate as [Last Edit],
      EditorNames as [Edited By],
      Posts.Score as [Score],
      Posts.FavoriteCount as [Favorites],
      coalesce(Posts.ViewCount, Parent.ViewCount) as [Views],
      Posts.AnswerCount as [Answers],
      iif(Posts.AcceptedAnswerId is not null or Posts.Id = Parent.AcceptedAnswerId, ''Accepted'', '''') as [Accepted],
      iif(Posts.CommunityOwnedDate is not null, ''CW'', '''') as [CW],
      iif(Posts.ClosedDate is not null, iif(Dupes.PostId is not null, ''Duplicate'', ''Closed''), '''') as [Closed]
    from
      PostsByUser
      join Posts on Posts.Id = PostsByUser.PostId
      left join PostTypes on PostTypes.Id = PostsByUser.PostTypeId
      left join Posts as Parent on Parent.Id = PostsByUser.ParentId
      left join LastRevisions on LastRevisions.PostId = PostsByUser.PostId
      left join PostHistory on PostHistory.Id = LastRevisions.LastRevId
      left join EditorsGrouped on EditorsGrouped.PostId = PostsByUser.PostId
      left join WikiTags on WikiTags.PostId = PostsByUser.PostId
      left join Dupes on Dupes.PostId = PostsByUser.PostId
      join #Sites on ''' + @sitedbname + ''' = #Sites.DatabaseName;

    delete from #PostsByUser;
  '
  execute(@query)
  FETCH NEXT FROM sites INTO @sitedbname
END
CLOSE sites
DEALLOCATE sites

select * from #Results

fork query

edited apr 30 Aaron Bertrand

created feb 13 19 anonymous