获取 SQL Server 中存储过程的文本 [英] Get the text of a stored procedure in SQL Server

问题描述

我正在尝试将旧的存储过程保存到字符串中.当我使用以下内容时，我没有任何换行符.

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?

我正在考虑使用 sp_helptext

谢谢

更新

我正在复制并粘贴结果中的结果，这将显示一行.

I am copying and pasting the results from the results which will show me a single line.

至于脚本，因为我将结果存储在 db 字段中.我正在创建一个可以动态生成程序的工具，但我想创建它们的历史记录.

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.

更新

原来 Object_definition 可以满足我的要求，但是由于某种原因，当我从结果中复制它时，我得到了一行.

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.

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

推荐答案

我最近遇到了同样的问题，并制作了一个快速而肮脏的脚本来获取视图的定义，但同样的事情也适用于存储程序和功能.

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 ;

它只是按照您的建议使用 sp_helptext，在表变量中获取其输出并将所有结果行连接到文本变量中.它还利用了 sp_helptext 结果集中的每一行都包含尾随换行符这一事实，因此无需在此处添加.

It simply uses sp_helptext as 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_helptext result set includes the trailing new line character, so no need to add it here.

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

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.

这篇关于获取 SQL Server 中存储过程的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！