Profile finder

1

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Different

Q&A for power users of Apple hardware and software

DECLARE @accountid int = (
  SELECT AccountId
  FROM Users
  WHERE Id = ##SiteUserId:int##
)

-- based in part on Glorfindel's query: https://data.stackexchange.com/stackoverflow/revision/1320872/1528722/users-with-a-certain-name-across-the-network

-- result table, don't rename and keep the site column
CREATE TABLE #results (site NVARCHAR(250),
                       displayname NVARCHAR(40),
                       id INT,
                       reputation INT);

DECLARE @sql NVARCHAR(MAX) = ''   -- holds build up sql string

-- build one biq union sql, for each db
SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
SELECT ''' + name + ''', displayname, id, reputation
  FROM ' + quotename(name) + '.dbo.Users
  WHERE AccountId = ' + STR(@accountid)

FROM sys.databases
WHERE database_id > 5
  AND (name = 'StackExchange.Meta' OR
  name NOT LIKE '%.Meta' )

-- execute it
EXEC (@sql)

SELECT
'Stack Exchange' as Site,
'' as 'Display name',
(SELECT SUM(Reputation) FROM #results WHERE Reputation >= 200) as Reputation,
'https://stackexchange.com/users/' + CAST(@accountid AS NVARCHAR) + '?tab=accounts' AS 'Link',
@accountid as 'User ID'
UNION ALL
SELECT
  CASE WHEN Site LIKE 'StackExchange.%' THEN
      RIGHT(Site, LEN(Site) - LEN('StackExchange.'))
    ELSE
      Site
    END
  AS Site,
  displayname as 'Display name',
  Reputation,
  CASE
    WHEN Site LIKE 'StackExchange.%' THEN
      'https://' + LOWER(RIGHT(Site, LEN(Site) - LEN('StackExchange.'))) + '.stackexchange.com/u/' + CAST(id as NVARCHAR)
    WHEN Site NOT LIKE '%.%' THEN
      'https://' + LOWER(Site) + '.com/u/' + CAST(id as NVARCHAR)
    WHEN Site LIKE 'StackOverflow.%' THEN
      'https://' + LOWER(RIGHT(Site, LEN(Site) - LEN('StackOverflow.'))) + '.stackoverflow.com/u/' + CAST(id as NVARCHAR)
    ELSE
      'Unknown'
    END
  AS 'Link',
  id as 'User ID'
  FROM #results
  ORDER BY
    Reputation DESC,
    Site ASC;
     
DROP TABLE #results

Enter Parameters

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