http://meta.stackexchange.com/questions/269804/dump-of-my-own-stackexchange-content
Q&A for researchers, developers, and users of Tor
---- 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 , body nvarchar(max) ); 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 , p.body from posts p inner join posts q on q.id = coalesce(p.parentid, p.id) inner join users pu on pu.id = p.owneruserid where pu.accountid = '+ cast(@accountid as nvarchar) + ';' exec (@sql) fetch next from db_c into @db_c_name end; close db_c; deallocate db_c; -- process results declare db_r cursor for select replace( replace( replace(site, '.StackExchange.com',''), '.com',''), '.net','') as [site] , 'http://' + site + '/q/' + cast(id as nvarchar) -- + '|' -- + title as question , title , body from #all_posts declare @db_r_body nvarchar(max) declare @db_r_site nvarchar(250) declare @db_r_title nvarchar(250) declare @db_r_url nvarchar(250) print '[' open db_r fetch next from db_r into @db_r_site, @db_r_url, @db_r_title, @db_r_body while(@@FETCH_STATUS = 0) begin print ',{' print 'prompt-type:' + @db_r_site + ',' print 'prompt:' + @db_r_title +',' print 'response:' + @db_r_body print '}' fetch next from db_r into @db_r_site, @db_r_url, @db_r_title, @db_r_body end; close db_r; deallocate db_r; print ']' -- plain output for CSV -- remove the comment start and end markers /* select replace( replace( replace(site, '.StackExchange.com',''), '.com',''), '.net','') as [site] , 'http://' + site + '/q/' + cast(id as nvarchar) -- + '|' -- + title as question , title , body from #all_posts */ -- end of CSV output -- clean up drop table #all_posts drop procedure #recursesiteurl drop procedure #siteurl