1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people interested in governments, policies, and political processes

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

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 + ''', accountid, displayname
  FROM ' + quotename(name) + '.dbo.Users
  WHERE displayname = ''' + ##username:string## + ''' collate sql_latin1_general_cp1_ci_ai
'
FROM sys.databases
WHERE database_id > 5
  AND (name = 'StackExchange.Meta' OR name NOT LIKE 'StackExchange.Meta%')

-- execute it
EXEC (@sql)

SELECT 'https://stackexchange.com/users/' + CAST(accountid AS NVARCHAR) AS 'User',
  string_agg(cast(site as nvarchar(max)), ', ') AS 'Sites',
  string_agg(cast(displayname as nvarchar(max)), ', ') AS 'Usernames'
  FROM #results
  GROUP BY accountid
  ORDER BY COUNT(*) DESC;
     
DROP TABLE #results

fork query

edited may 19 Makyen

created may 25 20 Glorfindel