Hey, this is neat. I hadn't found the sys_modules before. So much easier than having to concatenate the syscomments text field!

Run the following with results to text. It'll script out all the SPs. Doesn't matter what database it is run in either.

SET NOCOUNT ON

DECLARE @sText nvarchar(max),

@C cursor,

@ObjectID int

SET @C = cursor for

SELECT sp.object_id

from sys.procedures sp

WHERE is_ms_shipped = 0

AND left(sp.name, 2) = 'sp' -- might not want this!! Change as needed.

ORDER BY sp.name

OPEN @C

FETCH @C INTO @ObjectID

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sText = ''

SELECT @sText = sc.definition

FROM sys.sql_modules sc

WHERE sc.object_id = @ObjectID

PRINT '----------------------------------------------------------------------------'

SELECT '-- ' + object_name(@ObjectID)

PRINT '----------------------------------------------------------------------------'

PRINT @sText

PRINT 'GO'

PRINT '----------------------------------------------------------------------------'

FETCH @C INTO @ObjectID

END

CLOSE @C

DEALLOCATE @C

GO