Registered StackExchange Sites and User Details - Non-Registered sites are also included with URLs

1

Please login or register to vote for this query.

(click on this box to dismiss)

Audio-Video Production Meta

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

-----------------------------------------------------------------------------------------
-- This query is written by senshin (https://data.stackexchange.com/users/11293)       --
-- To see the original version of this query which includes all the comments           --
-- pls visit this link: https://data.stackexchange.com/space/query/341231              --
-- Pls Note that this query has been modified by the user to meet his/her requirements --
-----------------------------------------------------------------------------------------

DECLARE sites CURSOR FOR
  SELECT NAME
  FROM   sys.databases
  WHERE  NAME NOT IN ( 'master', 'tempdb', 'model', 'msdb', 'Data.StackExchange')
  
DECLARE @sitedbname AS NVARCHAR(max)
DECLARE @sitehostname AS NVARCHAR(max)
DECLARE @ispersitemeta AS BIT
DECLARE @query AS NVARCHAR(max)

CREATE TABLE #StackExchangeSites
  (
     SiteDbName   NVARCHAR(max),
     SiteHostName NVARCHAR(max)
  )

DECLARE @spooky_string AS NVARCHAR(max)
DECLARE @spooky_delimiter AS CHAR(1) = '.'
DECLARE @spooky_xml AS XML
DECLARE @spooky_result AS NVARCHAR(max)

OPEN sites
FETCH next FROM sites INTO @sitedbname

WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @spooky_string = @sitedbname
      SET @spooky_xml = Cast(( '<X>' + Replace(@spooky_string, @spooky_delimiter, '</X><X>') + '</X>' ) AS XML)
      SET @spooky_result = ''

      SELECT c.value('.', 'nvarchar(max)') AS [Piece],
             c.value('for $i in . return count(../*[. << $i]) + 1', 'int') AS [Index]
      INTO   #spooky_pieces
      FROM   @spooky_xml.nodes('X') AS X(c)

      SELECT @spooky_result = COALESCE(@spooky_result + '.', '') + [piece]
      FROM   #spooky_pieces
      ORDER  BY [index] DESC

      DROP TABLE #spooky_pieces

      SET @sitehostname = 'https://' + RIGHT(@spooky_result, Len(@spooky_result)-1) + '.com'
      SET @ispersitemeta = ( CASE WHEN @sitedbname LIKE '%Meta%' AND @sitedbname != 'StackExchange.Meta' THEN 1 ELSE 0 END )

      IF @sitedbname = 'StackExchange.Mathoverflow.Meta' SET @sitehostname = 'https://Meta.MathOverflow.net'

      IF @sitedbname = 'StackExchange.Audio' SET @sitehostname = 'https://Video.StackExchange.com'

      SET @query = 'USE [' + @sitedbname + ']

                    INSERT INTO #StackExchangeSites
                    SELECT''' + @sitedbname + '''   AS [SiteDbName],
                          ''' + @sitehostname + ''' AS [SiteHostName]'

      EXEC Sp_executesql @query
      FETCH next FROM sites INTO @sitedbname
  END
CLOSE sites
DEALLOCATE sites

--SELECT * FROM #StackExchangeSites

-----------------------------------------------------------------------------------------
-- This query is written by rene (https://data.stackexchange.com/users/11779)          --
-- To see the original version of this query which includes all the comments           --
-- pls visit this link: https://data.stackexchange.com/stackoverflow/query/998446      --
-- Pls Note that this query has been modified by the user to meet his/her requirements --
----------------------------------------------------------------------------------------- 

DECLARE @sql NVARCHAR(max)

CREATE TABLE #result
  (
     Site        SYSNAME,
     DisplayName NVARCHAR(MAX),
     ID          INTEGER,
     Upvotes     INTEGER,
     Downvotes   INTEGER,
     CountBadges INTEGER,
     AllBadges   NVARCHAR(MAX)
  ) 

SELECT @sql = N'insert into #result' + String_agg(Concat(N' select 
        ''', CONVERT(NVARCHAR(max), NAME), N''' as site      
        , u.displayname      
        , u.id      
        , u.upvotes      
        , u.downvotes       
        , count(b.Name) as CountBadges      
        , STRING_AGG(CAST(b.Name AS nvarchar(max)), '', '') as AllBadges 
    from ', CONVERT(NVARCHAR(max), Quotename(NAME)), N'.dbo.users u 
    inner join ', CONVERT(NVARCHAR(max), Quotename(NAME)), N'.dbo.Badges b on b.UserID = u.id  
    where accountid = ##StackExchangeNetworkProfileID## 
    group by u.displayname, u.id, u.upvotes, u.downvotes '),
       N'  union all')
FROM   sys.databases
WHERE  database_id > 5 AND (NAME NOT LIKE '%.Meta' OR NAME = 'StackExchange.Meta')

EXEC (@sql)

--SELECT * FROM #result

-----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------- 
 
SELECT se.sitedbname,
       se.sitehostname,
       us.displayname,
       us.id,
       us.upvotes,
       us.downvotes,
       us.countbadges,
       us.allbadges
FROM   #stackexchangesites se
       FULL OUTER JOIN #result us ON us.site = se.sitedbname
WHERE  se.sitedbname NOT LIKE '%Meta'
ORDER  BY 
       CASE WHEN us.countbadges IS NULL THEN se.sitedbname END ASC,
       CASE WHEN us.countbadges IS NOT NULL THEN us.countbadges END DESC

Enter Parameters

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