Generate scripts for all the SPs in the DB

-- of course you can replace objects type (not only SPs - all the objects that can use sp_helptext)

-- if you don't need the drop part - remark it

DECLARE @Cmd nvarchar(max), @Object nvarchar(1000)

DECLARE CursorDelSPs CURSOR FORWARD_ONLY  FOR 

-------------- Start Select --------------------------

select SCHEMA_NAME(schema_id) + '.' + name from sys.objects 

where type in ('P', 'PC', 'RF', 'X')

and SCHEMA_NAME(schema_id) = 'dbo'

-------------- End Select ----------------------------

OPEN CursorDelSPs

FETCH NEXT FROM CursorDelSPs 

INTO @Object

WHILE (@@FETCH_STATUS = 0)

BEGIN

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@Object) AND type in ('P', 'PC', 'RF', 'X'))

BEGIN

-- drop

print 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(' + @Object + ') AND type in (''P'', ''PC'', ''RF'', ''X''))

DROP PROCEDURE ' + @Object + '

GO

'

--header

print '/****** Object:  StoredProcedure ' + @Object + '    Script Date: ' + Convert(varchar, getdate(), 121) + ' ******/'

print 'SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO'

-- body

SET @Cmd = 'sp_helptext ''' + @Object + ''''

EXEC (@Cmd)

-- footer

print 'GO'

END

--Get next record from cursor

FETCH NEXT FROM CursorDelSPs 

INTO @Object

END

CLOSE CursorDelSPs

DEALLOCATE CursorDelSPs