0

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackexchange.com/questions/269804/dump-of-my-own-stackexchange-content

Q&A about the site for people looking to improve their interpersonal communication skills

---- 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

fork query

edited nov 11 anonymous

created nov 25 15 rene