How to iterate through each row in sql server?

Question

My query returns 26 table names.

select name from sys.tables where name like '%JPro_VP_Service%'

Now I'm trying to write a query to check in every table return from the above query.

--consider this is my first table 
select * from JPro_VP_Service 
where row_id like '%1-101%' or row_id like '%1-102%'
-- likewise I want to search in 26 tables return from above query    

I think I need to write for or cursor to accomplish this.

Can anyone help me how to achieve this?

Solution

The easiest way to do this is Try this:

SELECT 'select * from ' + name 
       + ' where row_id         like ''%1-101%'' or row_id like ''%1-102%''' 
FROM   sys.tables 
WHERE  name LIKE '%JPro_VP_Service%' 

you will get all tables together with the same conditions. You could execute them together.

Comments

[a_horse_with_no_name]

This sounds like your data model could use some re-design. If that is the same data in those table, it should be stored in a single table.

[Hanfeng]

cte can only be used in the clause SELECT * FROM @mn=Min(rn), @mx=Max(rn) FROM cte. When cte is used in the while loop, the sql server reports an error