Originally published on KendalVanDyke.com.
Ever wonder how to script SQL Server replication stored procedures? If you use SQL stored procedures to replicate commands there’s a very useful procedure in SQL 2000 SP1 and up which will generate the custom insert, update, and delete procedures for all articles in a transactional publication: sp_scriptpublicationcustomprocs. It takes only one parameter – the name of the publication – and is run at the publisher. Details are available in BOL here.
sp_scriptpublicationcustomprocs relies on other stored procedures to create the scripts, some of which have been around since the SQL Server 7.0 days. These SQL replication stored procedures are used to script individual articles and take an article ID as their only parameter (Article IDs are found by querying sysarticles in the published database). For CALL syntax the procedures sp_scriptinsproc, sp_scriptdelproc, and sp_scriptupdproc will generate the insert, delete, and update procedures, respectively. For XCALL syntax use sp_scriptxupdproc to generate the update procedure and sp_scriptxdelproc to generate the delete procedure. For MCALL and SCALL syntax use sp_scriptmappedupdproc to generate the update procedure. (Side note – if you don’t understand the differences between CALL, SCALL, MCALL, and XCALL check out Specifying How Changes Are Propagated for Transactional Articles in BOL)
The ability to script some or all of the procedures necessary for a publication is very useful for many reasons. The obvious is when you already have the data at the subscriber and set up a subscription without synchronization. Or you may want to modify the scripts to do something special at the subscriber. The not so obvious situations are the ones that make life as a DBA interesting. I’ve had developers who didn’t recognize “what all those sp_MS stored procedures were” in their database and included dropping them as part of their deployments. Oops. To be fair I’ve also dropped them unintentionally in moments of stupidity. You know that checkbox that says “Connect to Subscriber and delete record of this subscription at the Subscriber also” when you delete a subscription? Leave that checked next time you drop a subscription in a central subscriber topology and you’ll find out why sp_scriptpublicationcustomprocs is so handy.