Q&A for scientists using computers to solve scientific problems
-- 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 LIKE ''' + '%' + ##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