Many times using a cursor for this sort of thing seems like the only way to pull this off. sp_msforeachdb is just a cursor too. Neither of these approaches is bad but if you have a lot of databases it can be really slow. As another option you can use FOR XML to build a dynamic string.

Something like this. Once you are sure the dynamic string is correct just uncomment the sp_executesql. 🙂

declare @SQL nvarchar(max)

;with DatabaseList as

(

select name

from sys.databases

where database_id > 4

and name not in ('ReportServer', 'ReportServerTempDB')

)

select top 1 @SQL =

stuff((

select 'union all select Col1, col2, col3, Col4, col5 from ' + name + '.dbo.YourTable '

from DatabaseList

FOR XML PATH('')), 1, 10, '')

from DatabaseList

select @SQL

--exec sp_executesql @statement = @SQL