Script to generate replication procs for article

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[usp_scriptreplproc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_scriptreplproc]
GO

CREATE PROCEDURE dbo.usp_scriptreplproc
(
@dbname SYSNAME = NULL,
@articlename SYSNAME,
@action VARCHAR(6) = NULL
)
AS

/*
**  Purpose:   To quickly script replication stored procs without snapshoting
**  Author:    Richard Ding
**  Date:      1/30/2003
**  Modified by:  Sean Gorman
**  Date:    3/26/2007
**  Mods:  Now uses  sp_scriptdynamicupdproc for MCALL updates (SQL2K post SP3)
**Changed eval of sysarticles to use name column instead of dest_table
**Fixed Dynamic SQL USE statements
**Forced comments on "Procedure text"
**added NOCOUNT ON
**added "dummy" tip
**Added SQL 2005 Publisher Compatibility
**Added error message and checks for article not found
**
**  Test code:
**    sp_chi_scriptreplproc 'choice', 'actv_profil', 'del'
**    sp_chi_scriptreplproc 'waste_mgmt', 'drum_trackg_actvty', 'ins'
**    sp_chi_scriptreplproc 'reference', 'company_master', 'update'
**    sp_chi_scriptreplproc null, 'actv_profil', 'del'
**    sp_chi_scriptreplproc 'choic', 'actv_profil', 'delelte'
**    sp_chi_scriptreplproc 'order_mgmt', 'sls_order_dspsl', null
*/
SET NOCOUNT ON

DECLARE @version INT
DECLARE @type_get NVARCHAR(512)
DECLARE @retval VARCHAR(5)
DECLARE @string VARCHAR(800)
DECLARE @actionstring VARCHAR(800)
DECLARE @exists_string NVARCHAR(100)

CREATE TABLE #results
([results] TEXT)
CREATE TABLE #retval2
([retval2] INT)

SELECT @version = SUBSTRING((CONVERT(VARCHAR(25), SERVERPROPERTY('productversion')) ), 1, 1)

BEGIN
IF @dbname IS NULL
SET @dbname = db_name()
ELSE
BEGIN
DECLARE @returncode INT
EXEC @returncode = sp_validname @dbname
IF @returncode <> 0
BEGIN
RAISERROR(15224,-1,15,@dbname)
RETURN(1)
END
END
IF @version = 8
BEGIN
IF (SELECT category FROM master..sysdatabases WITH (NOLOCK) WHERE name = @dbname) = 0
BEGIN
DECLARE @errormsg8 VARCHAR(300)
SET @errormsg8 = 'Database ''' + @dbname + ''' is NOT published. You must pick a published database.'
RAISERROR (@errormsg8, 16, 1)
RETURN
END
END
IF @version = 9
BEGIN
IF (SELECT is_published FROM master.sys.databases WITH (NOLOCK) WHERE name = @dbname) = 0
BEGIN
DECLARE @errormsg9 VARCHAR(300)
SET @errormsg9 = 'Database ''' + @dbname + ''' is NOT published. You must pick a published database.'
RAISERROR (@errormsg9, 16, 1)
RETURN
END
END
SET @exists_string = N'SELECT artid FROM ' + @dbname + '.dbo.sysarticles WITH (NOLOCK) WHERE name = ''' + @articlename + CHAR(39)
INSERT INTO #retval2
EXEC (@exists_string)
IF NOT EXISTS (SELECT retval2 FROM #RETVAL2 WHERE retval2 IS NOT NULL)
BEGIN
DECLARE @errormsg VARCHAR(300)
SET @errormsg = 'Article ''' + @articlename + ''' cannot be found in ' + @dbname + '.dbo.sysarticles.  Please check the number and dial again.'
RAISERROR (@errormsg, 16, 1)
RETURN
END
IF @action IS NOT NULL AND @action NOT IN ('del', 'delete', 'ins', 'insert', 'upd', 'update')
BEGIN
RAISERROR ('Valid action types are ''del'', ''delete'', ''ins'', ''insert'', ''upd'', ''update''.  Please try again.', 16, 1)
RETURN
END

SELECT '--***TIP:  Hit CTRL + T to get results in text.***   '
PRINT CHAR(13)
PRINT 'USE ' + @dbname
PRINT CHAR(13)
PRINT 'GO'
PRINT CHAR(13) + CHAR(10)

SET @type_get = N'SELECT @retval = SUBSTRING(upd_cmd, 1, 5) FROM ' + @dbname + '..sysarticles WITH (NOLOCK) WHERE name = ' + CHAR(39) + @articlename + CHAR(39)
EXEC sp_executesql 
@query = @type_get
,@params = N'@retval VARCHAR(5) OUTPUT'
, @retval = @retval OUTPUT


SET @string = 'USE ' + @dbname + CHAR(10) + CHAR(13) +  'DECLARE @articleid INT'  + CHAR(10) + CHAR(13) +  'SET @articleid = ( SELECT artid FROM sysarticles WITH (NOLOCK) WHERE name = ''' + @articlename + ''')'  + CHAR(10) + CHAR(13)  


IF @action IN ('del', 'delete')
BEGIN
SET @actionstring = ' EXEC sp_scriptdelproc @artid = @articleid '
END

IF @action IN ('ins', 'insert')
BEGIN
SET @actionstring = ' EXEC sp_scriptinsproc @artid = @articleid '
END

IF @action IN ('upd', 'update')
BEGIN
IF @retval = 'XCALL'
BEGIN
SET @actionstring = 
'EXEC sp_scriptxupdproc @artid = @articleid '
END
IF @retval = 'MCALL'
BEGIN
SET @actionstring = 
'EXEC sp_scriptdynamicupdproc @artid = @articleid '
END
END
IF @action IS NULL
BEGIN
SET @actionstring =   
'EXEC sp_scriptdelproc @artid = @articleid '
 + CHAR(13) +  
'EXEC sp_scriptinsproc @artid = @articleid '
+ CHAR(13) 

IF @retval = 'XCALL'
BEGIN
SET @actionstring = @actionstring + 
'EXEC sp_scriptxupdproc @artid = @articleid '
END
IF @retval = 'MCALL'
BEGIN
SET @actionstring = @actionstring + 
'EXEC sp_scriptdynamicupdproc @artid = @articleid '
END
END

INSERT INTO #results
EXEC (@string + @actionstring)


SELECT results AS '--Procedure(s) Text Follows' FROM #results

END


GO