declare @dbNames nvarchar(max)
set @dbnames = 'VEPROMS_%' 
declare @CommandLine nvarchar(max)
set quoted_identifier off -- Allows single quotes to be used in the query'
set @CommandLine = "
 select Count(*) HowMany from ~rofsts where rolookup like '%@HSP(%'
"
 set quoted_identifier on
  declare @CommandLines nvarchar(max)
  select @CommandLines = Coalesce(@CommandLines + ' UNION' + char(10), '') + 'select ''' + Name + ''' DBName, * from ( ' + Replace(Replace(@CommandLine,'~','{Name}.dbo.'),'{Name}',Name) + ') T1'
  from Sys.Databases where name like @dbNames and name not like '%test'
  Set @CommandLines = 'Select * from ( ' +@CommandLines + ') t2 '--where HowMany > 0'
  print @CommandLines
  EXEC SP_EXECUTESQL @CommandLines