How can I get the text of a stored procedure into a single record in SQL Server 2000?

Question

Through the following script I get as a result all stored procedures in a database:

SELECT sm.id, OBJECT_NAME(sm.id) AS object_name, sm.text
FROM syscomments AS sm
JOIN sysobjects AS so ON sm.id = so.id
WHERE (so.status >= 0) AND (so.xtype = 'P')

I want to get one row per stored procedure. The text field in the table syscomments is nvarchar 4000. So a method may be stored in more than one register. The problem is that I have stored procedures with more than 15000 characters.

Identifying SP that use more than one record

SELECT sm.id, COUNT(sm.colid) AS Cantidad
INTO #SPrepeated
FROM  syscomments AS sm INNER JOIN
      sysobjects AS so ON sm.id = so.id
WHERE (so.status >= 0) AND (so.xtype = 'P') AND (so.category = 0)
GROUP BY sm.id, so.name
HAVING   (COUNT(sm.colid) > 1)

SELECT sm.id, OBJECT_NAME(sm.id) AS object_name, sm.text
into #Tresult
FROM syscomments AS sm
JOIN sysobjects AS so ON sm.id = so.id
JOIN #SPrepeated as spr ON so.id = spr.id
WHERE (so.status >= 0) AND (so.xtype = 'P')

select * from #Tresult

drop table #Tresult
drop table #SPrepeated

In #TResult I have all of the stored procedures that have more than one record in the table syscomments. How could these be grouped and concatenated into a single record per procedure, given that the result might exceed 8000 characters?

Answer 1

I called the SELECT statement from an application, as suggested @devio and it worked perfectly (the text field is not truncated). The script suggested by @LittleBobbyTables is the solution to the problem, with a little modification, since the first record is concatenated twice the same string.

Solution

-- Gonzalo's original code --
SELECT sm.id, COUNT(sm.colid) AS Cantidad
INTO #SPrepeated
FROM  syscomments AS sm INNER JOIN
  sysobjects AS so ON sm.id = so.id
WHERE (so.status >= 0) AND (so.xtype = 'P') AND (so.category = 0)
GROUP BY sm.id, so.name
HAVING   (COUNT(sm.colid) > 1)

SELECT sm.id, sm.colid, OBJECT_NAME(sm.id) AS object_name, 
    cast(sm.text as ntext) as [text]
into #Tresult
FROM syscomments AS sm
JOIN sysobjects AS so ON sm.id = so.id
JOIN #SPrepeated as spr ON so.id = spr.id
WHERE (so.status >= 0) AND (so.xtype = 'P')


-- LittleBobbyTables code

-- Create our #TresultSingle temporary table structure --    
SELECT TOP 1 [id], object_name, cast([text] as ntext) as [text]
INTO #TresultSingle
FROM #Tresult

-- Clear out the table, ready to insert --        
TRUNCATE TABLE #TresultSingle

DECLARE @id int, @previd int, @colid int, @objectname nvarchar(4000), 
    @text nvarchar(4000)
DECLARE @ptrval varbinary(16), @offset int
SET @text = ''
set @previd = 0;

-- Begin cursor, and start praying --   
DECLARE ResultCursor CURSOR
FOR 
SELECT [id], colid, [object_name], [text]
FROM #Tresult
ORDER BY [id], colid

OPEN ResultCursor

FETCH NEXT FROM ResultCursor
INTO @id, @colid, @objectname, @text

WHILE @@FETCH_STATUS = 0
BEGIN
-- If the ID has changed, create a new record in #TresultSingle --
IF @id <> @previd
BEGIN
    INSERT INTO #TresultSingle
    SELECT @id, @objectname, @text
END
ELSE
BEGIN
        -- Get the textpointer of the current @id --
        SELECT @ptrval = TEXTPTR(text) 
        FROM #TresultSingle
        WHERE [id] = @id 

        -- Set our offset for inserting text --
        SET @offset = 4000 * (@colid - 1)
        -- Add the new text to the end of the existing text --
        UPDATETEXT #TresultSingle.text @ptrval @offset 0 @text
    END

    SET @previd = @id

    FETCH NEXT FROM ResultCursor
    INTO @id, @colid, @objectname, @text
END

CLOSE ResultCursor
DEALLOCATE ResultCursor

SELECT * FROM #TresultSingle

DROP TABLE #TresultSingle
drop table #Tresult
drop table #SPrepeated