>Very nice. Clever stuff. I like this a lot.

Yes, it dawned on me one day that I can do lots of dynamic SQL this way. Check out my blog about it, I have about 8 different scripts in it and can write this kind of script quite easily now when I need to run some code for all tables or all databases or other objects.

See also - on the same theme
How to search a string value in all columns in all tables in a database

>
>>>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 - obvously I don't want to do this manually - any ideas ?
>>
>>Try:
>>

>>declare @SQL nvarchar(max)
>>
>>select @SQL = coalesce(@SQL + CHAR(13),'') + ';INSERT INTO Master SELECT * FROM ' + 
>>quotename(Table_Schema) + '.' + quotename(Table_Name)  from INFORMATION_SCHEMA.Tables
>>where Table_Name LIKE 'IOM%'
>>
>>--print @SQL -- to verify
>>
>>EXECUTE (@SQL)