I realize this post is old, but I found it handy for a problem I was working today.

One thing I added was to change the CREATE to an ALTER. This was to facilitate backing up an existing object.

Here is what my finished code looks like, should it help someone else.

SET NOCOUNT ON;

USE <DATABASENAME>;

DECLARE

@objName varchar(128);

SET @objName = <OBJECT NAME>;

/* Script out objects if they exist*/

IF EXISTS (SELECT *

FROM sys.objects

WHERE object_id

=

OBJECT_ID(@objName)

AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT',N'P'))

BEGIN

DECLARE

@t TABLE(line varchar(8000))

END;

INSERT INTO @t

EXEC sp_helptext @objName;

DECLARE

@ddl varchar(8000);

SELECT @ddl = '';

/* Change CREATE to ALTER because this object all ready exists */

SELECT @ddl = @ddl + REPLACE(line, 'CREATE procedure', 'ALTER PROCEDURE')

FROM @t;

PRINT @ddl;