Generate sql for cross database queries

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

select 'select accountid, ''' + replace([name],'StackExchange.','') +''' , (select count(*) from [' +[name]+ '].dbo.users )  from [' +[name]+ '].dbo.users where reputation > 200 and datediff(d, lastaccessdate, getdate()) < 60  union all '
from sys.databases 
where database_id > 5 -- skip master, temp, model, msdb, Data.SE
and name not like '%.Meta%'
and name not in (
  'StackExchange.Meta'
  , 'Stackoverflow'
  , 'ServerFault'
  , 'Superuser')
  


select accountid, 'Academia' , (select count(*) from [StackExchange.Academia].dbo.users ) from [StackExchange.Academia].dbo.users where reputation > 200 and datediff(d, lastaccessdate, getdate()) < 60


/*
select count(*) as cnt
       , 'SO' as site
from Users u
inner join all_accounts a on u.accountid = a.accountid
union all
*/

select 'select count(*), ''' + replace([name],'StackExchange.','') +''' from [' +[name]+ '].dbo.users u inner join all_users a on a.accountid = u.accountid  and a.site <> ' + cast(database_id as nvarchar) + ' union all '
from sys.databases 
where database_id > 5 -- skip master, temp, model, msdb, Data.SE
and name not like '%.Meta%'
or name = 'StackExchange.Meta'

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results