如何在SQL Server 2000中将存储过程的文本获取到单个记录中？

Question

通过以下脚本，我可以获取数据库中所有存储过程的结果：

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

我希望每个存储过程只返回一行。表syscomments中的文本字段是nvarchar 4000。因此，一个方法可能会被分成多个记录存储。问题在于，我有一些存储过程超过15000个字符。

识别使用多个记录的存储过程

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

在 #TResult 中，我拥有所有在表 syscomments 中有多条记录的存储过程。如何将它们分组并连接成每个存储过程的单个记录，考虑到结果可能超过8000个字符？

- Gonzalo

Answer 1

如果你不得不使用SQL Server 2000，你几乎必须使用text或者ntext字段来突破nvarchar和varchar限制的4000/8000个字符。但是你不能在text和ntext字段上使用普通的字符串命令，这会变成一团糟。

如果你真的想要将每个存储过程放在单独的一行中，可以尝试以下方法。

下面的代码实际上是个灾难，我使用了游标、READTEXT和UPDATETEXT，基于你的基本查询创建了一个存储过程列表，这些存储过程在单独的一行中。代码中的注释应该有助于识别我正在尝试做什么。

这并没有经过100%的测试，所以如果有什么问题请告诉我。

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

- LittleBobbyTables - Au Revoir

---

我相信有人会在这方面提出一些问题，但希望这将是一个很好的起点，只需要进行一些微调。 - LittleBobbyTables - Au Revoir

感谢@LittleBobby的回复。我已经测试过了，不幸的是它将文本字段截断为8192个字符。 - Gonzalo

Answer 2

即使@LittleBobbyTables的解决方案正确（我没有测试，所以我只是假设它是正确的），这也不会返回SP的完整文本：

在EM和SSMS中，SELECT根据所提到的选项截断结果。PRINT也会截断，无论任何选项，在8000字节处。

我建议从C＃应用程序调用SELECT语句，而不是使用本机T-SQL。

- devio

---

是的，我忘了SQL Server不允许超过8192个字符。至少这是一个有趣的练习。 - LittleBobbyTables - Au Revoir

Answer 3

我从应用程序中调用了SELECT语句，如@devio所建议的，它完美地工作了（文本字段未被截断）。@LittleBobbyTables建议的脚本是解决问题的方法，稍作修改，因为第一条记录重复连接了同一个字符串。

解决方案

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

- Gonzalo