获取 SQL Server 中存储过程的文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30266936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get the text of a stored procedure in SQL Server
提问by H20rider
I am trying to save an old stored procedure into a string. When I use the following I do not get any line breaks.
我正在尝试将旧的存储过程保存到字符串中。当我使用以下内容时，我没有得到任何换行符。
SELECT @OldProcedure = Object_definition(object_id)
FROM sys.procedures
WHERE name = @ProcedureName
Any suggestions on how to get the stored procedure text with the line breaks?
关于如何使用换行符获取存储过程文本的任何建议？
I was thinking of using sp_helptext
我想用 sp_helptext
Thanks
谢谢
Update
更新
I am copying and pasting the results from the results which will show me a single line.
我正在复制和粘贴结果中的结果，这将显示一行。
As for the script as I am storing the results in a db field. I am create a tool that will generate procedures on the fly, but I wanted to create a history of them.
至于脚本，因为我将结果存储在 db 字段中。我正在创建一个可以动态生成程序的工具，但我想创建它们的历史记录。
Update
更新
Turned out the Object_definition does what I want, But for some reason when I copy it from the results I get a single line.
结果 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
回答by Alejandro
I've recently come across the same question and made a quick and dirty script to get the definition of views, but the very same thing could also work for stored procedures and functions.
我最近遇到了同样的问题，并制作了一个快速而肮脏的脚本来获取视图的定义，但同样的事情也适用于存储过程和函数。
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 ;
It simply uses sp_helptextas you suggested, grabs its output in a table variable and concatenates all the resulting lines into a text variable. It also uses the fact that each line in the sp_helptextresult set includes the trailing new line character, so no need to add it here.
它只是sp_helptext按照您的建议使用，在表变量中获取其输出并将所有结果行连接到文本变量中。它还利用了sp_helptext结果集中的每一行都包含尾随换行符这一事实，因此无需在此处添加。
From there on, you just use the variable as you would do normally, print it, save to some table or do some manipulation on it. My particular use case was to build a helper stored procedure to drop a view and recreate it when modifying its underlying tables.
从那时起，您只需像往常一样使用变量、打印它、保存到某个表或对其进行一些操作。我的特定用例是构建一个辅助存储过程来删除视图并在修改其基础表时重新创建它。
回答by Ben Thul
Two ways:
两种方式：
select name, object_definition(object_id)
from sys.procedures
or
或者
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
回答by BrianAtkins
I would highly recommend just using the "Script To" function in SQL Server Management Studio:
我强烈建议只使用 SQL Server Management Studio 中的“Script To”功能：
I have had great use of this in the past, when dealing with old objects like Stored Procedures.
过去，在处理诸如Stored Procedures.
回答by Anon
It sounds like you want to log the text of a procedure every time it changes. You can do this automatically with a DDL Trigger. Unlike DML triggers, which fire on changes to the data, these will fire on changes to the database.
听起来您想在每次更改时记录程序的文本。您可以使用DDL 触发器自动执行此操作。与 DML 触发器不同，它会在数据更改时触发，而这些触发器将在数据库更改时触发。
Create a table to hold the audit log
创建一个表来保存审计日志
CREATE TABLE DDLAudit_Procedure_log (event_instance xml);
Create a trigger to populate the audit log table
创建触发器以填充审计日志表
CREATE TRIGGER trg__DDLAudit_Procedure
ON DATABASE
FOR
CREATE_PROCEDURE
,ALTER_PROCEDURE
AS
BEGIN
INSERT DDLAudit_Procedure_log
(event_instance)
SELECT EVENTDATA();
END
After your autogenerated procedures are created, check the log
创建自动生成的程序后，检查日志
SELECT *
FROM DDLAudit_Procedure_log
WHERE event_instance.value('(//ObjectName)[1]','sysname') = 'MyAutoGeneratedProc'
ORDER BY event_instance.value('(//PostTime)[1]','datetime')
This is a simple example. You can examine the contents of EVENTDATA()within the trigger to filter for specific tables. The MSDN help page has more detail.
这是一个简单的例子。您可以检查EVENTDATA()触发器内的内容以筛选特定表。MSDN 帮助页面有更多详细信息。
