accounts that use more then 2 displaynames

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

declare @sql as nvarchar(max)

create table #allusers( id integer
                      , accountid integer
                      , displayname nvarchar(40))

create index #idx_allusers_accountid on #allusers(accountid)

select @sql = N'insert into #allusers ' + string_agg(
concat(N'select ', convert(nvarchar(max),database_id) , N'
 , accountid 
, displayname 
from ', convert(nvarchar(max),quotename(name)), N'.dbo.users 
where accountid is not null
'), N'union all  
')
from sys.databases
where database_id> 5
and (name not like '%.meta' or name = 'Stackexchange.meta')
-- and name not in ('stackoverflow')
print @sql

exec (@sql)

select count(*) [accounts with more then 1 username]
from
(
  select accountid from
  (
    select accountid, count(*) cnt
    from #allusers
    group by accountid, displayname
  ) data
  group by accountid
  having count(*) > 1
) alldata

Enter Parameters

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