I am writing several papers in which I use answers which I received in various StackExchange websites. Naturally I would like to acknowlege and thank the SE members that gave the useful answers. But there are many such answers, and each answer is relevant to a different paper.
So, I would like to download all the questions that I asked in the various SE websites, including the answerer names, to an Excel worksheet in which I can organize the questions by subject.
Is this possible?
You already got a per site query from Patrick and that leaves the cross-site-query for me.
This query outputs all your questions network-wide with the answers given, listing score, creationdate and the user (linked).
The query requires an accountid and that one can be found by visiting your network account on StackExchange.com and then take the id from the url. Yours is 439169, mine is 281857.
---- accountid: Account on stackexchange.com! "In the url on stackexchange.com"
-- start create url from dbname
IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL
DROP PROCEDURE #siteurl;
GO
create procedure #siteurl
@dbname nvarchar(250),
@res nvarchar(250) OUT
as
begin
-- we have three sites that have borked DNS entries
set @dbname = (case @dbname
when 'StackExchange.Audio' then 'StackExchange.Avp'
when 'StackExchange.Audio.Meta' then 'StackExchange.Avp.Meta'
when 'StackExchange.Mathoverflow.Meta' then 'net.Mathoverflow.meta'
else @dbname
end)
-- and one of those doesn't want to end with .com
if @dbname <> 'net.Mathoverflow.meta'
set @dbname = 'com.' + @dbname
exec #recursesiteurl @dbname, @res OUTPUT
end
GO
-- create url from dbname
IF OBJECT_ID ( '#recursesiteurl', 'P' ) IS NOT NULL
DROP PROCEDURE #recursesiteurl;
GO
create procedure #recursesiteurl
@dbname nvarchar(250),
@res nvarchar(250) OUT
as
begin
declare @pos integer
declare @rec nvarchar(250)
set @res = @dbname
set @pos = CHARINDEX('.', @dbname)
if (@pos > 0 )
begin
set @rec = substring(@dbname, @pos+1, len(@dbname))
exec #recursesiteurl @rec, @rec output
set @res = @rec
+ '.'
+ substring(@dbname, 0, @pos)
end
end;
GO
declare @accountid int = ##accountid:int## -- 93484
-- all databases
declare db_c cursor for select [name]
from sys.databases
where database_id > 5 -- skip master, temp, model, msdb, Data.SE
declare @db_c_name sysname -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string
-- result table
create table #all_posts ( site nvarchar(250)
, id int
, title nvarchar(200)
, aid int
, creationdate datetime
, score int
, bodyexcerpt nvarchar(100)
, owneruserid int
, displayname nvarchar(200));
open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
set @sql = N'use '+ QUOTENAME(@db_c_name) +';
declare @url nvarchar(250)
exec #siteurl ''' + @db_c_name + ''', @url output
insert into #all_posts
select @url
, q.id
, q.title
, p.id
, p.creationdate
, p.score
, left(p.body,100)
, p.owneruserid
, pu.displayname
from posts p
inner join posts q on p.parentid = q.id
inner join users pu on pu.id = p.owneruserid
inner join users qu on qu.id = q.owneruserid
where qu.accountid = '+ cast(@accountid as nvarchar) + ';'
exec (@sql)
fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;
select replace(
replace(
replace(site,
'.StackExchange.com',''),
'.com',''),
'.net','') as [site]
, 'http://'
+ site
+ '/q/'
+ cast(id as nvarchar)
+ '|'
+ title as question
, 'http://'
+ site
+ '/a/'
+ cast(aid as nvarchar)
+ '|answer' as AnswerLink
, score
, creationdate
--, bodyexcerpt
, 'http://'
+ site
+ '/users/'
+ cast(owneruserid as nvarchar)
+ '|'
+ displayname as [user]
from #all_posts
drop table #all_posts
drop procedure #recursesiteurl
drop procedure #siteurl
I start with creating a procedure (functions are not allowed) to translate a databasename to a site url. The db's are named StackExchange.Photography.meta and the url for that site is meta.photography.stackexchange.com
A temporary table is going to hold all our data. Then I open a cursor to iterate over all databases (except the system db's). In that loop I create a sql statement to switch to the correct database, create an insert/select to insert your posts with the answers in the temp table. When that is done the sql statement is executed.
After the loop is done the final select statement produces the result where I buildup the final url to the answers, question and user. The links work because SEDE interprets column values that contain http://site/url/tosomething|title as link and the front end renders a nice <a href="http://site/url/tosomething">title</a> for us.
Yes, you can using SEDE.
Here is the query you want:
select q.Id [Question Id]
, q.Id [Post Link]
, a.Id [Answer Id]
, a.Id [Post Link]
, a.OwnerUserId
, a.OwnerUserId [User Link]
from posts q
join posts a
on q.Id = a.ParentId
join users u
on a.OwnerUserId = u.Id
where q.PostTypeId = 1
and q.OwnerUserId = ##userid##
You can download the results as CSV, which you can import into Excel. I am not sure how to make this query cross-site, maybe someone else can enlighten me on that, but you can run it one-by-one for your sites.
