在SQL Server中获取存储过程的文本

Question

我正在尝试将一个旧的存储过程保存为字符串。当我使用以下代码时，没有出现任何换行符。

SELECT @OldProcedure = Object_definition(object_id)
FROM   sys.procedures
WHERE  name = @ProcedureName

如何获取带有换行符的存储过程文本？我想使用 sp_helptext。

谢谢。

更新

我正在从结果中复制和粘贴结果，这将显示单行文本。至于脚本，我正在将结果存储在数据库字段中。我创建了一个可以动态生成存储过程的工具，但我想创建它们的历史记录。

更新

结果发现 Object_definition 执行我所需的操作，但是由于某种原因，当我从结果中复制时，它会变成单行文本。

Declare @sql varchar(max) ;
SELECT @sql=Object_definition(object_id)
FROM   sys.procedures
WHERE  name = 'Test_QueryBuilder';

drop procedure Test_QueryBuilder
exec( @sql)
print @sql

- H20rider

---

你怎么知道没有换行符？你是在 SSMS 中查看吗？如果是的话，你只需要将输出设置为文本，而不是网格，它就会显示格式化文本。 - Sean Lange

为什么不直接在 SQL Server Management Studio 中查看它们，然后选择“生成脚本”或在查询窗口中执行呢？ - BrianAtkins

sp_helptext确实是最好的选择。您将获得存储过程在服务器上编译时/将要编译时的确切定义。 - Louis

这听起来像是一个 XY 问题。如果你想在数据库对象上进行版本控制，有更好的方法。http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem - Anon

@Anon。可能是这样的。基本上，我构建了一个生成存储过程的工具。我想保留所有生成的存储过程的历史记录。如果这属于XY问题，请告诉我。 - H20rider

Answer 1

两种方式：

select name, object_definition(object_id) 
from sys.procedures

或者

select object_name(p.object_id), definition
from sys.sql_modules as m
join sys.procedures as p
   on m.object_id = p.object_id

- Ben Thul

---

这是更正确的答案，已经进行了修正以确保正确性。 - Siderite Zackwehdex

Answer 2

最近我遇到了同样的问题，并快速制作了一个简单脚本来获取视图的定义，但同样的方法也适用于存储过程和函数。

DECLARE @Lines TABLE (Line NVARCHAR(MAX)) ;
DECLARE @FullText NVARCHAR(MAX) = '' ;

INSERT @Lines EXEC sp_helptext 'sp_ProcedureName' ;
SELECT @FullText = @FullText + Line FROM @Lines ; 

PRINT @FullText ;

就像你建议的那样，它简单地使用sp_helptext，将其输出抓取到一个表变量中，然后将所有结果行连接成文本变量。它还利用了sp_helptext结果集中每一行包含尾随换行符的事实，因此这里不需要添加它。

从那里开始，您只需像通常一样使用变量，打印它，保存到某个表或对其进行某些操作。我的特定用例是构建一个辅助存储过程，在修改其基础表时删除视图并重新创建它。

- Alejandro

---

可以这样做。但是你只需要添加DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)。 - H20rider

为什么？这行代码在我的示例中如何适用？ - Alejandro

Answer 3

我强烈建议在SQL Server Management Studio中使用"Script To"功能：

过去我在处理旧对象如存储过程时，曾经大量使用这个功能，非常有用。

- BrianAtkins

---

将结果保存还是在新的数据库中创建对象？如果您想将数据库对象保存在表中，那么您的用例是什么？ - BrianAtkins

是的，那绝对可行。在这种情况下，我正在自动化某些东西。 - H20rider

如果您没有权限查看/运行存储过程，这是行不通的。 - undefined

Answer 4

看起来你想在每次过程更改时记录文本。你可以使用DDL触发器来自动完成此操作。与DML触发器不同，DDL触发器将在数据库更改时触发。

创建一个表来保存审计日志

CREATE TABLE DDLAudit_Procedure_log (event_instance xml);

创建触发器以填充审计日志表。

CREATE TRIGGER trg__DDLAudit_Procedure
ON DATABASE
FOR
  CREATE_PROCEDURE
 ,ALTER_PROCEDURE
AS
BEGIN
  INSERT DDLAudit_Procedure_log
        (event_instance)
  SELECT EVENTDATA();
END

在生成自动化过程后，请检查日志。

SELECT *
FROM DDLAudit_Procedure_log
WHERE event_instance.value('(//ObjectName)[1]','sysname') = 'MyAutoGeneratedProc'
ORDER BY event_instance.value('(//PostTime)[1]','datetime')

这是一个简单的例子。您可以在触发器中检查 EVENTDATA() 的内容，以过滤特定的表格。MSDN 帮助页面有更多详细信息。

- Anon

Answer 5

建议使用 "sp_helptext" 获取正确的格式。

- udorb b