I need to replicate image catalog records from one server to another who's database names start with p_ I can hard code it like so (one out of many statements):

delete
from      p_PhotoDB.dbo.item_keyword
where     not exists (
    select  null as nothing
    from    OtherServer.p_PhotoDB.dbo.item_keyword new
    where   p_PhotoDB.dbo.item_keyword.item_id = new.item_id and
            p_PhotoDB.dbo.item_keyword.keyword_id = new.keyword_id
)

I know I could put the entire thing in a string variable and exec (@variable), but this would compile each time for each statement for each catalog ...

Is there a way to change the p_PhotoDB reference for each found database starting with p_ without using some form of exec (@variable)?

Dr Zim.,

To the best of my knowledge, that can't be done at the database level.

There are, of course, tools that can help you do this such as Redgate Compare, or built into various versions of Visual Studio.

If you need a way to automate this in a periodically running script, you might be able to use SYNONYMS for tables, but there is definitely some up front work required there, and it is definitely not useful in a number of situations. SEE: http://msdn.microsoft.com/en-us/library/ms177544.aspx

I hope this helps.