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

If you're forced to use SQL Server 2000, you pretty much have to use text or ntext fields to get past the 4000/8000 character limit for nvarchar and varchar respectively.

You can't use regular string commands on text and ntext fields, making it a big hot mess.

If you really want each stored procedure in a single row, you can try the following.

The code below is an abomination, no two ways about it. I've taken your base queries, and using cursors, READTEXT and UPDATETEXT, created a list of stored procedures in a single line. The comments in the code should hopefully help identify what I'm trying to do.

This hasn't been 100% tested, so let me know if anything doesn't work.

-- 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')

-- 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 = ''

-- 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

INSERT INTO #TresultSingle
SELECT @id, @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