0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for road warriors and seasoned travelers

-- result table, don't rename and keep the site column
create table #results (site nvarchar(250)
                       , reason nvarchar(500));

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 + ''', MarkdownInputGuidance
  FROM ' + quotename(name) + '.dbo.CloseAsOffTopicReasonTypes
  WHERE DeactivationDate IS NULL
'
from sys.databases
where database_id > 5

-- execute it
exec (@sql)

SELECT site, COUNT(*)
  FROM #results
  GROUP BY site
  ORDER BY COUNT(*) DESC

DROP TABLE #results;

fork query

edited jun 20

created dec 7 18 Glorfindel