Users with a certain name across the network

0

Please login or register to vote for this query.

(click on this box to dismiss)

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

-- username: User display name
DECLARE @username nvarchar(40) = ##username:string##
-- like: Full match(0)/Contains(1)
DECLARE @like int = ##like:int?0##

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

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 ' + IIF(@like = 0, '= ''', 'LIKE ''%') + @username +
  IIF(@like = 0, '', '%') + ''' 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) + '?tab=accounts' AS 'User',
  MIN(displayname) as 'Display name',
  string_agg(cast(site as nvarchar(max)), ', ') AS 'Sites'
  FROM #results
  GROUP BY accountid
  ORDER BY COUNT(*) DESC;
     
DROP TABLE #results

Enter Parameters

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