Query Help

Just started working at a place which has a SP which runs a loop and gets
the id.
Then this id is used as follows:
SELECT name, 0 from tbl
WHERE name like '%'+id+'%' AND xtype = 'u'
Basically to get the names of the tables that have the "id" as part of the
name. How can I avoid going in a loop on this query, say if I have the list
of IDs in a temp table.
Thanks.Try,
select distinct
a.table_name
from
information_schema.tables as a
inner join
ids as b
on a.table_name like '%' + col_id + '%'
and a.table_type = 'base table'
order by
a.table_name
AMB
"XXX" wrote:

> Just started working at a place which has a SP which runs a loop and gets
> the id.
>
> Then this id is used as follows:
> SELECT name, 0 from tbl
> WHERE name like '%'+id+'%' AND xtype = 'u'
>
> Basically to get the names of the tables that have the "id" as part of the
> name. How can I avoid going in a loop on this query, say if I have the lis
t
> of IDs in a temp table.
>
> Thanks.
>
>