Stack Exchange
log in help

Dump of my own Stack Exchange content

2

Please login or register to vote for this query.

(click on this box to dismiss)

Forked from https://meta.stackexchange.com/a/269951

Drones and Model Aircraft

Q&A for experienced flyers, pilots, and builders of drones and model aircraft

---- accountid: Stack Exchange account ID

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

-- 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]
         , 'https://' + site + '/q/' + CAST(id AS nvarchar) AS question
         , title
         , body
FROM #all_posts
ORDER BY site, question


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 '<!DOCTYPE html><html><head><meta charset="utf-8"></head><body>'
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 '<!-- start of q -->'
    PRINT '<div>'
    PRINT '<div>'
    PRINT '<div>site:' + @db_r_site + '</div>'
    PRINT '<div>url:' + @db_r_url + '</div>'
    PRINT '<div>title:' + @db_r_title + '</div>'
    PRINT '</div>'
    PRINT CAST(@db_r_body AS ntext)
    PRINT '</div>'
    PRINT '<!-- end of q -->'
    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 '</body></html>'

-- clean up
DROP TABLE #all_posts
DROP PROCEDURE #recursesiteurl
DROP PROCEDURE #siteurl
fork query

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results