>Pete,
>
>I posted the simple script in my other message. I suggest you to check this blog post for understanding of the idea
>
>
How to get information from all databases without a loop>
>>>You can get the list of tables with following query. Loop through it, build and execute INSERT INTO...SELECT FROM for each table.
>>>
>>>SELECT table_name
>>> FROM INFORMATION_SCHEMA.Tables
>>> WHERE TABLE_NAME LIKE 'IOM%'
>>>
Thanks again
>>>
>>>>Hi All, I have a couple of hundred tables in SS that have a prefix of IOM in their name, they all have the same structure which is a single column. What I want to do is create a master table containing all the records of these tables - obviously I don't want to do this manually - any ideas ?
>>
>>Thanks Sergey, how do loop through the list of tables ?, I've tried selecting a table name into a variable - but how do I select from a table using my variable ?
>>
>>this is what I've tried
>>
>>
>>
>>declare @fname varchar(max)
>>
>>Select @fname = min(table_name)
>>from information_schema.tables
>>where table_type = 'base table'
>>and left(table_name,3) = 'IOM'
>>
>>
>>
>>While @fname != null
>>begin
>>
>>
>>
>>Select @fname = min(table_name)
>>from information_schema.tables
>>where table_type = 'base table'
>>and left(table_name,3) = 'IOM'
>>and table_name > @fname
>>
>>end
>>
>>
Regards,
Peter J. Kane
Pete