2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

create table #validaccounts(accountid int)

declare @sql nvarchar(4000)

set @sql = N'insert into #validaccounts 
select distinct Accountid 
from (select u.Accountid 
from Users u
inner join ' + quotename(db_name()) + N'.dbo.Users h on h.Accountid = u.Accountid
where u.Reputation > 1
) validUsersPerSite'

print @sql

exec sede_ineachdb @sqlcommand = @sql
, @IncludeMetaSites = 0
, @CollectResultsForMe = 0

select id as [User Link]
     , views [0 views means BOT created]
       , concat('site://users/##urlpart##/', id,'|direct link') [link]
from users
left outer join #validaccounts va on va.accountid = users.accountid
where reputation = 1
and va.accountid is null
and (displayname = 'spam' COLLATE Latin1_General_100_CI_AI
or displayname = 'Spammer McSpamface' COLLATE Latin1_General_100_CI_AI
or displayname = 'spammer' COLLATE Latin1_General_100_CI_AI
or displayname like '%spam%' COLLATE Latin1_General_100_CI_AI)
order by 2, creationdate

select ##cachebreaker##

fork query

edited may 6

created aug 29 21 rene