select o.name, m.definition
 from master.sys.system_sql_modules m
  join master.sys.system_objects o
    ON o.object_id = m.object_id

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

--======
SET NOCOUNT ON

select N'
----------------------------------------------------------------------------
-- ' + p.name + N'
----------------------------------------------------------------------------
' + m.definition + N'
GO
----------------------------------------------------------------------------
'
 from sys.sql_modules m
  join sys.procedures p
    ON p.object_id = m.object_id
 WHERE is_ms_shipped = 0
    AND left(p.name, 2) = 'sp' -- might not want this!! Change as needed.
 ORDER BY p.name

ALTER function [dbo].[fnSplit3]( 
                @parameter varchar(Max)                -- the string to split
                , @Seperator Varchar(64)        -- the string to use as a seperator
) 
        RETURNS @Items TABLE(
                ID INT                                                -- the element number
                , item VARCHAR(8000)                -- the split-out string element
                , OffSet int                                -- the original offest
                --( not entirley accurate if LEN(@Seperator) > 1 because of the Replace() )
) 
AS
BEGIN 
/*
"Monster" Split in SQL Server 2005 

From Jeff Moden, 2008/05/22

BYoung, 2008/06/18: Modified to be a Table-Valued Function
                                And to handle CL/LF or LF-only line breaks
        (Note: make it inline later, to make it faster)

Test: (scripts all triggers in your database)

        Select Lines.Item
         From sys.sql_modules M
          Join sys.objects O on O.object_id = M.object_id
          cross apply dbo.fnSplit1(M.definition, char(13)+char(10)) Lines
         Where O.Type = 'TR' 
         Order by O.create_date, Lines.ID
*/
Declare @Sep char(1)
Set @Sep = char(10)        --our seperator character (convenient, doesn't affect performance)
--NOTE: we make the @Sep character LF so that we will automatically
-- parse out rogue LF-only line breaks.

--===== Add start and end seprators to the Parameter so we can handle
        -- all the elements the same way
        --  Also change the seperator expressions to our seperator
        -- character to keep all offsets = 1
SET @Parameter = @Sep+ Replace(@Parameter,@Seperator,@Sep) +@Sep
-- This reduces run-time about 10%

;WITH cteTally AS
(--==== Create a Tally CTE from 1 to whatever the length
        -- of the parameter is
 SELECT TOP (LEN(@Parameter))
        ROW_NUMBER() OVER (ORDER BY t1.ID) AS N
  FROM Master.sys.sysColumns t1
   CROSS JOIN Master.sys.sysColumns t2
)
INSERT into @Items
        SELECT ROW_NUMBER() OVER (ORDER BY N) AS Number,
                SUBSTRING(@Parameter, N+1, CHARINDEX(@Sep, @Parameter, N+1)-N-1) AS Value
                , N+1
         FROM cteTally
         WHERE N < LEN(@Parameter)
          AND SUBSTRING(@Parameter, N, 1) = @Sep --Notice how we find the seperator

        Return 
END

--======
SET NOCOUNT ON

Select [item]
 From (select p.name, 0 as [num], N'
----------------------------------------------------------------------------
-- ' + p.name + N'
----------------------------------------------------------------------------
' as [item]
	 from sys.sql_modules m
	  join sys.procedures p ON p.object_id = m.object_id
	 WHERE is_ms_shipped = 0

 UNION ALL
	Select p.name, Lines.ID as num, Lines.item
	 from sys.sql_modules m
	  join sys.procedures p ON p.object_id = m.object_id
	  Cross Apply dbo.fnSplit3(m.definition, (char(13)+char(10))) Lines
	 WHERE is_ms_shipped = 0

 UNION ALL
	Select p.name, 999999,N'
GO
----------------------------------------------------------------------------
	' as [item]
	 from sys.procedures p
	 WHERE is_ms_shipped = 0
) S
 WHERE left([name], 2) = 'sp' -- might not want this!! Change as needed.
 ORDER BY [name], [num]

DECLARE @A nvarchar(max)
SELECT @A = ISNULL(@A,'') + OBJECT_DEFINITION(object_id)  + '
GO
' FROM sys.procedures
SELECT CAST(@A AS XML)

DECLARE @A nvarchar(max)
SELECT @A = ISNULL(@A,'') + OBJECT_DEFINITION(object_id)  + '
GO
' FROM sys.procedures
SELECT CAST(@A AS XML)

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Msg 9421, Level 16, State 1, Line 5
XML parsing: line 149, character 30, illegal name character