We have several SQL Server 2000 databases (I know, we need to upgrade) that have the same structure and have them set up to replicate to another server. The problem is that whenever I have to change the structure (which is usually pretty easy to do on all databases, especially with tools from Red Gate) I have to stop the replication, make the structure changes, and then set up replication again. The steps to set up replication only take a few minutes to do for each database but it's repetitive and drives me crazy. I have the IDE create a script of the replication procedure and then just replace the name of the prior database with the name of the next database and run the script. Still annoying but faster than clicking though the IDE and forgetting an option.
I've tried things like using the "SP_MSFOREEACHDB" but that didn't look very promising.
My guess is I should just use the TSQL that gets generated from the IDE and use that as a starting point to build a new TSQL script have it pass the name of the database as a parameter. And then when something changes with the structure of the database I need to address that in the TSQL replication script and make the changes there. Is this an issue for anyone else? Does 2005 or 2008 have a better "SP_MSFOREACHDB" so I wouldn't have to mainting some crazy script and just have the IDE generate a script when there are changes that I could then use on multiple databases easily?