{Li C Zhao}

In the past few months, I’ve been working more closely with Database because the departure of our DBA. I have been doing deployments that are involved in adding and altering schema changes. Whenever a new table is added to SQL replication you need to generate replication store procedures at the publisher and the take the result and put it at the subscribers. In the past, I would need to run ’sp_scriptpublicationcustomprocs ‘, which generate store procedures for every published tables, and I would search for store procedures for the new table. Next, I would need to find the article id for the new table and run ’sp_scriptdynamicupdproc ‘, which will generate smarter update store procedure.

I’ve create a store procedure that will only generate store procedures that for specified table name. If no table name is supplied, it will generate store procedures for every published articles.

-- Author: Li C Zhao
-- Version: 1.0
-- Date: 2008-02-29
-- Usage: exec sp_GenerateReplProcedures [@table = 'tablename']
-- Description: Generate store procedures for replication for the
--    subscribers. If no parameter is pass, it will generate all
--    published store procedures. If table name is passed as the
--    parameter, it will generate store procedures (INSERT, UPDATE,
--    and DELETE) for the specified article (if found).
--
CREATE PROCEDURE [sp_GenerateReplProcedures]
    @table VARCHAR(255) = NULL
AS

SET NOCOUNT ON
CREATE TABLE #TmpSpText ( spText NVARCHAR(2048) )

IF @table IS NOT NULL
    DECLARE test CURSOR FOR SELECT artid FROM [sysarticles]
        WHERE dest_table = @table
ELSE
    DECLARE test CURSOR FOR SELECT artid FROM [sysarticles]
OPEN test

DECLARE @id INT
FETCH NEXT FROM test INTO @id

WHILE @@fetch_status<>-1
BEGIN
    -- Generating Insert Store Procedure
    INSERT INTO #TmpSpText (spText)
        EXEC sp_scriptinsproc @id

    SELECT spText AS '---- INSERT STORE PROCEDURE' FROM [#TmpSpText]
    PRINT 'GO'
    DELETE FROM #TmpSpText

    -- Generating Delete Store Procedure
    INSERT INTO #TmpSpText (spText)
        EXEC sp_scriptdelproc @id
    SELECT spText AS '---- DELETE STORE PROCEDURE' FROM [#TmpSpText]
    PRINT 'GO'
    DELETE FROM #TmpSpText

    -- Generating Dynamic Update Procedure
    INSERT INTO #TmpSpText (spText)
        EXEC sp_scriptdynamicupdproc  @id

    SELECT spText AS '---- UPDATE STORE PROCEDURE' FROM #TmpSpText
    PRINT 'GO'
    DELETE FROM #TmpSpText

FETCH NEXT FROM test INTO @id
END
CLOSE test
DEALLOCATE test
DROP TABLE #TmpSpText

GO

Links:
sp_scriptdynamicupdproc
sp_scriptpublicationcustomprocs