Everytime we need to alter any schema, we need to generate the entire schema change script along with replication script. We follow following steps.
1> Script to Drop subscription
2> Script to Drop article
3> Script to Alter the schema (write Alter TableProcedure script)
4> Script to Add article
5> Script to add subcription
So even a simple alter table script becomes very big due to the extra script we write for adddrop subcription, article.
At present the SQL server doesnt provide any way to generate ALTER tableprocedure script Along with Replication script. If you try to get the SQL script for a tableprocedure, the SQL server gives you Alter tableprocedure script without adddrop subcriptionarticle script. (there is no option such as "generate script with replication").
Please let us know the simpler way to generate script with replication, to altercreate any sql server 2000 object.
What you describe is the proper and appropriate way to handle a schema change (though if you have certain 3rd party software like Red Gates SQL Compare you at least have choices on how do step 3.)
Of course, the other thing to consider is that you probably do not want a large number of schema changes in production whether or not it is replicated.
answered Aug 12 '10 at 06:29 AM