you can do the following:
use master
declare @dbname varchar(100)
,@sql varchar(max)
create table #TempDBs (
dbname nvarchar(100)
, RecordCount int
)
declare db_cur cursor for
SELECT name
FROM master.dbo.sysdatabases where dbid>4
open db_cur
fetch next from db_cur into @dbname
while @@FETCH_STATUS = 0
begin
set @sql='insert into #TempDbs(
dbname,
RecordCount
)
select '''+@dbname+''' DbName, COUNT(*) count FROM '+@dbname+'.dbo.MyLeads WHERE active = 1'
exec(@sql)
fetch next from db_cur into @dbname
end
close db_cur
deallocate db_cur
select * from #TempDBs order by DbName
drop table #TempDBs
To make the results cleaner, I inserted into a temp table, then queried the temp table.