declare @sql varchar(max)
declare @i int
set @i = 1
while @i <= 30
begin
select @sql = isnull(@sql+' union all ','') + ' select * from r'+ltrim(@i)
set @i = @i + 1
end

print @sql
exec(@sql)