Q&A for active researchers, academics and students of physics
-- result table, don't rename and keep the site column CREATE TABLE #results (site nvarchar(250), postid int, score int, tags nvarchar(250), creationdate datetime, title 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 + ''', p.Id, p.Score, p.Tags, p.CreationDate, p.Title FROM ' + quotename(name) + '.dbo.Posts AS p INNER JOIN ' + quotename(name) + '.dbo.Users AS u ON p.OwnerUserId = u.Id WHERE u.AccountId = ' + CAST(##AccountId:int## as nvarchar) + ' AND p.PostTypeId = 1 -- Questions AND p.Body COLLATE SQL_Latin1_General_CP1_CI_AS LIKE ''%' + ##query:string## + '%'' ' FROM sys.databases WHERE database_id > 5 -- AccountId: The global account ID of the user, which you can find in the URL of their network profile. Visit this link: http://stackexchange.com/users/current to see your own ID. -- execute it EXEC (@sql) SELECT site, LOWER('https://' + IIF(PATINDEX('%.Mathoverflow%', site) > 0, IIF(PATINDEX('%.Meta', site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'), IIF(PATINDEX('%.Ubuntu%', site) > 0, IIF(PATINDEX('%.Meta', site) > 0, 'meta.askubuntu.com', 'askubuntu.com'), IIF(PATINDEX('StackExchange.%', site) > 0, CASE SUBSTRING(site, 15, 200) WHEN 'Audio' THEN 'video' WHEN 'Audio.Meta' THEN 'video.meta' WHEN 'Beer' THEN 'alcohol' WHEN 'Beer.Meta' THEN 'alcohol.meta' WHEN 'CogSci' THEN 'psychology' WHEN 'CogSci.Meta' THEN 'psychology.meta' WHEN 'Garage' THEN 'mechanics' WHEN 'Garage.Meta' THEN 'mechanics.meta' WHEN 'Moderators' THEN 'communitybuilding' WHEN 'Moderators.Meta' THEN 'communitybuilding.meta' WHEN 'Photography' THEN 'photo' WHEN 'Photography.Meta' THEN 'photo.meta' WHEN 'Programmers' THEN 'softwareengineering' WHEN 'Programmers.Meta' THEN 'softwareengineering.meta' WHEN 'Vegetarian' THEN 'vegetarianism' WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta' WHEN 'Writers' THEN 'writing' WHEN 'Writers.Meta' THEN 'writing.meta' ELSE SUBSTRING(site, 15, 200) END + '.stackexchange.com', IIF(PATINDEX('StackOverflow.%', site) > 0, CASE SUBSTRING(site, 15, 200) WHEN 'Br' THEN 'pt' WHEN 'Br.Meta' THEN 'pt.meta' ELSE SUBSTRING(site, 15, 200) END + '.stackoverflow.com', IIF(PATINDEX('%.Meta', site) > 0, 'meta.' + SUBSTRING(site, 0, PATINDEX('%.Meta', site)) + '.com', site + '.com' ) ) ) ) )) + '/q/' + CAST(postid AS NVARCHAR) + '|' + title, score, tags, creationdate FROM #results ORDER BY creationdate DESC; DROP TABLE #results