Bob,

Thanks. This looks like exactly what I'm looking for, especially sp_MSforeachdb!



>Have you looked at using the sp_MSforeach???? procedures to do some of these tasks... for more info check out this article...
>
>http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/5D30F6DBB37272C1852568E2006A94B6
>
>>I am attempting to write a tsql script that will replicate a given operation across all databases on a given sql server (they all have the same tables, sp's etc). So far, I have written the code below. But I don't know of a way to reference a variable name in a tsql statement, or perform the equivalent of macro substition in vfp
>>For example, for each database in the schema, I want to be able to 'use it', and then do some further operation on it, like create a stored procedure. But the code doesnt work. In the code below the USE statement (which is commented out) doesn't work, nor does the statement:
>>select * from @ccatlogname.archived
>>
>>Is this possible in tsql?
>>
>>TIA
>>
>>
>>
>>
>>declare @cCatalogName char(100)
>>
>>declare dblist scroll cursor
>>for
>>select catalog_name from master.information_schema.schemata
>>
>>OPEN dblist
>>
>>
>>
>>FETCH NEXT FROM dblist
>>INTO @cCatalogName
>>print @ccatalogname
>>WHILE @@FETCH_STATUS = 0
>>BEGIN
>> FETCH NEXT FROM dblist
>> INTO @cCatalogName
>> SET @ccatalogname=rtrim(@ccatalogname)
>> print @ccatalogname
>>
>> /*use @ccatalogname */
>> select * from @ccatlogname.archived
>>END
>>
>>CLOSE dblist
>>deallocate dblist