SET NOCOUNT ON

-- //////////////////////////////////////////////////////////
-- -> Temporary procedure to spilit into lines
IF (OBJECT_ID('tempdb..#usp_print_lines') IS NOT NULL)
   DROP PROCEDURE #usp_print_lines
GO
CREATE PROCEDURE #usp_print_lines
   @text NVARCHAR(MAX)
AS
   DECLARE @ret TABLE (id INT IDENTITY, line NVARCHAR(MAX))

   DECLARE @pos INT, @next INT, @crlf CHAR(2)
   SELECT @pos = 1, @crlf = CHAR(13) + CHAR(10)

   WHILE (1 = 1)
   BEGIN
      SELECT @next = CHARINDEX(@crlf, @text, @pos)
      IF (@next = 0) BREAK -- Nothing more to do
      
      IF (@pos != @next)
         INSERT INTO @ret SELECT SUBSTRING(@text, @pos, @next - @pos)

      SELECT @pos = @next + 1
   END

   SELECT line FROM @ret ORDER BY id -- Return lines
GO
-- <- Temporary procedure to spilit into lines
-- //////////////////////////////////////////////////////////


DECLARE @now DATETIME, @duration INT, @crlf CHAR(2), @count INT

DECLARE @tally TABLE (N INT NOT NULL, PRIMARY KEY CLUSTERED (N))
DECLARE @source TABLE (name NVARCHAR(128), definition NVARCHAR(MAX))
DECLARE @result TABLE (line nvarchar(max))

SELECT @crlf = CHAR(13) + CHAR(10)

-- //////////////////////////////////////////////////////////
-- -> Test data and tally table

-- Get some system procedures to split into lines
INSERT INTO @source
   SELECT TOP(200) o.name, @crlf + m.definition + @crlf
   FROM master.sys.all_objects o
      JOIN master.sys.all_sql_modules m ON o.object_id = m.object_id
   WHERE type = 'P'

INSERT INTO @tally
   SELECT TOP(100000) ROW_NUMBER() OVER (ORDER BY c1.column_id)
   FROM master.sys.all_columns c1 CROSS JOIN master.sys.all_columns c2

-- <- Test data and tally table
-- //////////////////////////////////////////////////////////

-- //////////////////////////////////////////////////////////
-- -> Tally solution
PRINT 'Start tally solution'
SELECT @now = GETDATE()

-- Split text into lines
INSERT INTO @result 
   SELECT l.line
   FROM @source s
      CROSS APPLY (SELECT TOP(1000) SUBSTRING(s.definition, N + 2, CHARINDEX(@crlf, s.definition, N + 1) - N - 2) line
                   FROM @tally
                   WHERE N < LEN(s.definition) - 1
                      AND SUBSTRING(s.definition, N, 2) = @crlf
                   ORDER BY N
                   ) l

-- Results
SELECT @duration = DATEDIFF(MILLISECOND, @now, GETDATE())
SELECT @count = COUNT(*) FROM @result
PRINT 'Milliseconds: ' + CONVERT(VARCHAR(10), @duration) + ' | Lines: ' + CONVERT(VARCHAR(10), @count)
-- <- Tally solution
-- //////////////////////////////////////////////////////////

DELETE FROM @result  -- Clean up

-- //////////////////////////////////////////////////////////
-- -> Cursor solution
PRINT 'Start cursor solution'
SELECT @now = GETDATE();

-- Create another table and copy all data...
DECLARE @procs table (name nvarchar(128), definition NVARCHAR(MAX))

INSERT INTO @procs
   SELECT name, definition
   FROM @source

DECLARE @name NVARCHAR(128)
DECLARE @definition NVARCHAR(MAX)

-- Loop through all data and split into lines
WHILE EXISTS (SELECT TOP(1) 1 FROM @procs)
BEGIN
   SELECT TOP(1) @name = name, @definition = definition FROM @procs
   
   -- Split current text into lines
   INSERT INTO @result
      EXECUTE #usp_print_lines @definition
   
   DELETE FROM @procs WHERE name = @name
END

-- Results
SELECT @duration = DATEDIFF(MILLISECOND, @now, GETDATE())
SELECT @count = COUNT(*) FROM @result
PRINT 'Milliseconds: ' + CONVERT(VARCHAR(10), @duration) + ' | Lines: ' + CONVERT(VARCHAR(10), @count)
-- <- Cursor solution
-- //////////////////////////////////////////////////////////

DECLARE @crlf NCHAR(2)
SELECT @crlf = CHAR(13) + CHAR(10)

SELECT	SUBSTRING(s.definition, N + 2, CHARINDEX(@crlf, s.definition, N + 1) - N - 2)
FROM	#source AS S
JOIN	#tally AS T
		ON (T.N BETWEEN 1 AND  S.Length - 2)
WHERE	SUBSTRING(s.definition, N, 2) = @crlf
ORDER	BY N

--===== Procedure to split line
     IF (OBJECT_ID('tempdb..#usp_print_lines') IS NOT NULL)
        DROP PROCEDURE #usp_print_lines
GO
 CREATE PROCEDURE #usp_print_lines
        @text NVARCHAR(MAX)
     AS
DECLARE @ret TABLE (id INT IDENTITY, line NVARCHAR(MAX))
DECLARE @pos INT, @next INT, @crlf NCHAR(2)

 SELECT @pos = 1, @crlf = CHAR(13) + CHAR(10)
   
  WHILE (1 = 1)
  BEGIN
         SELECT @next = CHARINDEX(@crlf, @text, @pos)

             IF (@next = 0) BREAK --- Nothing more to do

             IF (@pos <> @next)
         INSERT INTO @ret 
         SELECT SUBSTRING(@text, @pos + 1, @next - @pos -1)

         SELECT @pos = @next + 1
     END

 SELECT line FROM @ret ORDER BY id --- Return lines
GO