All my comments on the SE network

1

Please login or register to vote for this query.

(click on this box to dismiss)

Want to archive your comments in case Stack Exchange goes down? Just enter your SE network account ID below, run the query and download the resulting CSV file. For downloading your questions and answers, use https://data.stackexchange.com/stackoverflow/query/983612

Stack Apps

Q&A for apps, scripts, and development with the Stack Exchange API

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

create table #Results (
  [Site] nvarchar(128) not null,
  [Comment ID] int not null,
  [Created] datetime,
  [Score] int,
  [Text] nvarchar(max),
  [Post ID] int,
  [Post Type] nvarchar(50),
  [Post Author] nvarchar(40),
  [Post Title] nvarchar(250),
)

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

DECLARE sites CURSOR FOR
  SELECT name
  FROM sys.databases
  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange')

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 #Results
    select
      ''' + @sitedbname + ''' as [Site],
      Comments.Id as [Comment ID],
      Comments.CreationDate as [Created],
      Comments.Score as [Score],
      Comments.Text as [Text],
      Comments.PostId as [Post ID],
      PostTypes.Name as [Post Type],
      coalesce(Users.DisplayName, Posts.OwnerDisplayName) as [Post Author],
      coalesce(Posts.Title, Parent.Title) as [Post Title]
    from
      Comments
      left join Posts on Posts.Id = Comments.PostId
      left join PostTypes on PostTypes.Id = Posts.PostTypeId
      left join Posts as Parent on Parent.Id = Posts.ParentId
      left join Users on Users.Id = Posts.OwnerUserId
    where
      Comments.UserId = @localuserid
  '
  EXEC sp_executesql @query
  FETCH NEXT FROM sites INTO @sitedbname
END
CLOSE sites
DEALLOCATE sites

select * from #Results

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results