在SQL Server中将存储过程的文本获取到一个变量中

Question

我想要遍历多个存储过程并从每个存储过程中提取一个字符串以在另一个存储过程中使用（基本上是4部分的远程服务器字符串）。

所以我可以从SysObjects（类型为P）获取存储过程列表并将其放入一个表中，然后我可以循环或游标遍历该表变量并调用sp_helptext。

但是，我如何将sp_helptext的结果文本存储到变量中，以便我可以对单词'BOCTEST'等进行CharIndex操作？

是否有像sys.procedures这样存储文本的表。

- Our Man in Bananas

---

这不像是一个好主意。有什么使用情况吗？ - Kaspars Ozols

停止使用sysobjects和sp_存储过程。您需要使用sys.sql_modules。 - Aaron Bertrand

@Joe，那个“table”不存在。你可能指的是兼容视图syscomments，但不应该使用它——它已经被弃用，随时可能被移除，并且会将过程分成多个块。 - Aaron Bertrand

抱歉，是的 - 我指的就是那个。是的，它会在超过一个限制（4KB？8KB？我现在记不清了）时将代码分成块。是的，你应该不再使用它 - 但事实上，每当需要时，我仍然发现它非常方便，可以编程地解析“实时”对象的代码。 - Joe Pineda

@AaronBertrand：感谢您的有益评论，但是您可以送给一个人一条鱼，让他今天能吃饱；或者... - Our Man in Bananas

Answer 1

最便携的方法是使用ANSI/ISO视图INFORMATION_SCHEMA.ROUTINES，但您只能获得存储过程定义的前4000个字符：

declare @source_code varchar(max)

select @source_code = t.ROUTINE_DEFINITION
from information_schema.routines t
where specific_schema = 'owner-schema'             -- e.g., dbo
  and specific_name   = 'my_stored_procedure_name' -- your stored procedure name here

您也可以使用系统视图sys.sql_modules：

以同样的方式进行操作：

declare @source_code varchar(max)

select @source_code = definition
from sys.sql_modules
where object_id = object_id('dbo.my_stored_procedure_name')

或者，最简单的方式是：

declare @source_code varchar(max)
set @source_code = object_definition( 'dbo.my_stored_procedure_name' )

- Nicholas Carey

---

除非程序大于4000个字符，否则您将从INFORMATION_SCHEMA获得任意块。 几年前我提出了避免便携式解决方案的其他几个原因，但我甚至忘记争论这个事实。在SQL Server中正确的方法是使用sys.sql_modules或object_definition()。 - Aaron Bertrand

列 INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION 的文档（http://technet.microsoft.com/en-us/library/ms188757.aspx）说明它“*如果函数或存储过程未加密，则返回函数或存储过程定义文本的前4000个字符。否则，返回 NULL。*”由于底层数据存储为 varchar(max)，因此除了简单截断之外的任何行为都会令人惊讶。但我不能说我曾经测试过这些东西。 - Nicholas Carey

抱歉，你是对的，我混淆了INFORMATION_SCHEMA的不良行为和sys.syscomments的不良行为。然而，它并不是varchar(max) - 定义存储在底层目录中为nvarchar(max)，但information_schema将其截断为nvarchar(4000)。 - Aaron Bertrand

故事在SO21724490：从存储过程定义中提取字符串上继续。 - Our Man in Bananas

@Philip：请看我对那个问题的回答。 - Nicholas Carey

Answer 2

Nicholas Carey 的第三个选项需要做一些更改，

object_definition 函数需要 object_id 作为参数，

所以代码应该是

declare @source_code varchar(max)
declare @objectid int
select @objectid=object_id('dbo.my_stored_procedure_name')
select @source_code = object_definition(@objectid )

请点击以下链接查看更多详细信息： https://msdn.microsoft.com/en-IN/library/ms176090.aspx

- Virendra Yadav

Answer 3

您可以使用系统存储过程sp_helptext将存储过程定义获取到表中，然后只需针对该临时表/表变量查询类似以下的内容即可。

DECLARE @TABLE TABLE
(Line_Number INT IDENTITY(1,1), Text_Column NVARCHAR(MAX));

INSERT INTO @TABLE(Text_Column)
EXECUTE sp_helptext Your_Proc_Name

SELECT * 
FROM @TABLE
WHERE Text_Column LIKE '%BOCTEST%'

更新

SELECT p.name
      ,m.[definition]
FROM  sys.procedures p 
INNER JOIN sys.sql_modules m
ON    P.[object_id] = m.[object_id]
WHERE m.[definition] LIKE '%BOCTEST%'

- M.Ali

---

这根本不是一个好主意。你知道在过程中，每一行都会有一个单独的行吗？你怎么能指望从这个表中以任何有意义的方式查询呢？ - Aaron Bertrand

@AaronBertrand 你认为这个怎么样？这个解决方案可以帮助用户在过程定义中定位他想要查找的单词吗？ - M.Ali

现在他如何“循环执行多个过程”？你的表如何告诉他们某个行来自哪个过程？此外，您能保证IDENTITY值将按预期顺序生成吗？为什么要使用产生不良输出的旧sp_过程和表格，而不是使用更新的目录视图和函数呢？ - Aaron Bertrand

@AaronBertrand，非常感谢您的反馈。我已经使用了sys.procedures目录视图和OBJECT_DEFINITION()函数。您认为这种方法比上一种更好吗？ - M.Ali

我实际上更喜欢使用简单的联接到 sys.sql_modules，因为它将遵守事务隔离语义（而 OBJECT_* 函数不会），并且也适用于数据库前缀。 - Aaron Bertrand