blocks|key|224457|text|如果我在设置了Results+to+grid的情况下运行sp_helptext，然后将网格中的结果复制并粘贴到新的查询或任何其他文本编辑器中，则可以复制此行为。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|224458|这似乎是sp_helptext与以前版本相比的行为变化，因为这种效果不会在标准网格结果集中显示。|224459|最简单的解决方法是使用快捷键设置运行sp_helptext+(Query+->+Results+to+>+Results+to+text，快捷方式CTRL+%2B+T+)。|224460|您可能需要增加Results+to+text中每行的最大字符数，以获得预期的输出-+Tools+>+Options+>+Query+Results+>+Results+to+text+-将“每列中显示的最大字符数”设置为最大值8192。|224461|entityMap^0|7|F|S|B|0|4|B|0|I|B|V|5|14|A|1H|F|21|4|28|1|0|7|F|16|5|1E|7|1O|D|24|F|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]|$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|V|8|@$9|W|A|X|B|C]|$9|Y|A|Z|B|C]|$9|10|A|11|B|C]|$9|12|A|13|B|C]|$9|14|A|15|B|C]|$9|16|A|17|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|18|8|@$9|19|A|1A|B|C]|$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]|$9|1H|A|1I|B|C]]|D|@]|E|$]]|$1|L|3|-4|5|6|7|1J|8|@]|D|@]|E|$]]]|M|$]]

I can replicate this behaviour if I run <code>sp_helptext</code> with <code>Results to grid</code> set, then copy and paste the results from grid into a new query or any other text editor.

This seems to be a change in the behaviour of <code>sp_helptext</code> from previous editions, since this effect isn't displayed with standard grid result sets.

The simplest work-around will be to run <code>sp_helptext</code> with <code>Results to text</code> set (<code>Query</code> -> <code>Results to</code> > <code>Results to text</code>, shortcut <code>CTRL</code> + <code>T</code>.

You may need to increase the maximum number of characters per line in <code>Results to text</code> to get the output you expect - <code>Tools</code> > <code>Options</code> > <code>Query Results</code> > <code>Results to text</code> - set "maximum number of characters displayed in each column" to the maximum value of 8192.

blocks|key|2876932|text|在我看来，一个更好的解决方法(与将结果转换为文本相比)是创建一个sp_helptext2+storedproc，如下所述：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2876933|http://sql-javier-villegas.blogspot.com/2012/08/a-workaround-for-sphelptext-bug-in-ssms.html|offset|length|2876934|注意:该解决方案有一个bug，如果末尾没有新行，则会省略最后一行。更正的T-SQL：|2876935|CREATE+PROCEDURE+[dbo].[sp_helptext2]+(@ProcName+NVARCHAR(256))
AS
BEGIN
++DECLARE+@PROC_TABLE+TABLE+(X1++NVARCHAR(MAX))

++DECLARE+@Proc+NVARCHAR(MAX)
++DECLARE+@Procedure+NVARCHAR(MAX)
++DECLARE+@ProcLines+TABLE+(PLID+INT+IDENTITY(1,1),+Line+NVARCHAR(MAX))

++SELECT+@Procedure+=+'SELECT+DEFINITION+FROM+'%2Bdb_name()%2B'.SYS.SQL_MODULES+WHERE+OBJECT_ID+=+OBJECT_ID('''%2B@ProcName%2B''')'

++insert+into+@PROC_TABLE+(X1)
++++++++exec++(@Procedure)

++SELECT+@Proc=X1+from+@PROC_TABLE

++WHILE+CHARINDEX(CHAR(13)%2BCHAR(10),@Proc)+>+0
++BEGIN
++++++++INSERT+@ProcLines
++++++++SELECT+LEFT(@Proc,CHARINDEX(CHAR(13)%2BCHAR(10),@Proc)-1)
++++++++SELECT+@Proc+=+SUBSTRING(@Proc,CHARINDEX(CHAR(13)%2BCHAR(10),@Proc)%2B2,LEN(@Proc))
++END
++--*+inserts+last+line
++insert+@ProcLines+
++select+@Proc+;

++SELECT+Line+FROM+@ProcLines+ORDER+BY+PLID
END|code-block|syntax|javascript|2876936|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|2K|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@$D|V|E|W|1|X]]|A|$]]|$1|F|3|G|5|6|7|Y|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|Z|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|C]]]]

A better workaround (compared to use Results to text) in my opinion is to create an sp_helptext2 storedproc as explained here: 

<a href="http://sql-javier-villegas.blogspot.com/2012/08/a-workaround-for-sphelptext-bug-in-ssms.html">http://sql-javier-villegas.blogspot.com/2012/08/a-workaround-for-sphelptext-bug-in-ssms.html</a>

Note: that solution has a bug leaving out the last line if there is no new line at the end. 
Corrected T-SQL: 

<pre><code>CREATE PROCEDURE [dbo].[sp_helptext2] (@ProcName NVARCHAR(256))
AS
BEGIN
 DECLARE @PROC_TABLE TABLE (X1 NVARCHAR(MAX))

 DECLARE @Proc NVARCHAR(MAX)
 DECLARE @Procedure NVARCHAR(MAX)
 DECLARE @ProcLines TABLE (PLID INT IDENTITY(1,1), Line NVARCHAR(MAX))

 SELECT @Procedure = 'SELECT DEFINITION FROM '+db_name()+'.SYS.SQL_MODULES WHERE OBJECT_ID = OBJECT_ID('''+@ProcName+''')'

 insert into @PROC_TABLE (X1)
 exec (@Procedure)

 SELECT @Proc=X1 from @PROC_TABLE

 WHILE CHARINDEX(CHAR(13)+CHAR(10),@Proc) &gt; 0
 BEGIN
 INSERT @ProcLines
 SELECT LEFT(@Proc,CHARINDEX(CHAR(13)+CHAR(10),@Proc)-1)
 SELECT @Proc = SUBSTRING(@Proc,CHARINDEX(CHAR(13)+CHAR(10),@Proc)+2,LEN(@Proc))
 END
 --* inserts last line
 insert @ProcLines 
 select @Proc ;

 SELECT Line FROM @ProcLines ORDER BY PLID
END
</code></pre>

blocks|key|221093|text|我写了另一个替代CrLf的变通方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|221094|DECLARE+@results+table+([Text]+nvarchar(255))

INSERT+@results
Exec+sp_helptext+spStudyRoleCacheFlushNotificationGet

SELECT+REPLACE(REPLACE([Text],+CHAR(10),+''),+CHAR(13),+'')
FROM+@results|code-block|syntax|javascript|221095|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I wrote another workaround that just replaces the CrLf:

<pre><code>DECLARE @results table ([Text] nvarchar(255))

INSERT @results
Exec sp_helptext spStudyRoleCacheFlushNotificationGet

SELECT REPLACE(REPLACE([Text], CHAR(10), ''), CHAR(13), '')
FROM @results
</code></pre>

blocks|key|2876947|text|Rufo发布的答案仍然会产生空行。最后一行代码中的一些小改动为我解决了这个问题。以下是编辑后的代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2876948|CREATE+PROCEDURE+[dbo].[sp_helptext2]+(@ProcName+NVARCHAR(256))
AS
BEGIN
++DECLARE+@PROC_TABLE+TABLE+(X1++NVARCHAR(MAX))

++DECLARE+@Proc+NVARCHAR(MAX)
++DECLARE+@Procedure+NVARCHAR(MAX)
++DECLARE+@ProcLines+TABLE+(PLID+INT+IDENTITY(1,1),+Line+NVARCHAR(MAX))

++SELECT+@Procedure+=+'SELECT+DEFINITION+FROM+'%2Bdb_name()%2B'.SYS.SQL_MODULES+WHERE+OBJECT_ID+=+OBJECT_ID('''%2B@ProcName%2B''')'

++insert+into+@PROC_TABLE+(X1)
++++++++exec++(@Procedure)

++SELECT+@Proc=X1+from+@PROC_TABLE

++WHILE+CHARINDEX(CHAR(13)%2BCHAR(10),@Proc)+>+0
++BEGIN
++++++++INSERT+@ProcLines
++++++++SELECT+LEFT(@Proc,CHARINDEX(CHAR(13)%2BCHAR(10),@Proc)-1)
++++++++SELECT+@Proc+=+SUBSTRING(@Proc,CHARINDEX(CHAR(13)%2BCHAR(10),@Proc)%2B2,LEN(@Proc))
++END
+--*+inserts+last+line
+insert+@ProcLines+
+select+@Proc+;

+--edited+here.+(where+Line<>'')
+SELECT+Line+FROM+@ProcLines+where+Line<>''+ORDER+BY+PLID
END|code-block|syntax|javascript|2876949|尽管这删除了“原始回车”，但这是更好的生活方式。我用DB从SQL2008迁移到SQL2012，在使用sp_helptext时，所有的存储过程(超过200个)在每一行代码后都返回了大约5个回车符。|2876950|上面的代码帮助我解决了这个问题。|2876951|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

The answer posted by Rufo still produces blank lines. A little change in the last line of code solved the issue for me. Here is the edited code:

<pre><code>CREATE PROCEDURE [dbo].[sp_helptext2] (@ProcName NVARCHAR(256))
AS
BEGIN
 DECLARE @PROC_TABLE TABLE (X1 NVARCHAR(MAX))

 DECLARE @Proc NVARCHAR(MAX)
 DECLARE @Procedure NVARCHAR(MAX)
 DECLARE @ProcLines TABLE (PLID INT IDENTITY(1,1), Line NVARCHAR(MAX))

 SELECT @Procedure = 'SELECT DEFINITION FROM '+db_name()+'.SYS.SQL_MODULES WHERE OBJECT_ID = OBJECT_ID('''+@ProcName+''')'

 insert into @PROC_TABLE (X1)
 exec (@Procedure)

 SELECT @Proc=X1 from @PROC_TABLE

 WHILE CHARINDEX(CHAR(13)+CHAR(10),@Proc) &gt; 0
 BEGIN
 INSERT @ProcLines
 SELECT LEFT(@Proc,CHARINDEX(CHAR(13)+CHAR(10),@Proc)-1)
 SELECT @Proc = SUBSTRING(@Proc,CHARINDEX(CHAR(13)+CHAR(10),@Proc)+2,LEN(@Proc))
 END
 --* inserts last line
 insert @ProcLines 
 select @Proc ;

 --edited here. (where Line&lt;&gt;'')
 SELECT Line FROM @ProcLines where Line&lt;&gt;'' ORDER BY PLID
END
</code></pre>

Even though this removes the "original carriage returns", but this is way better to live with. I migrated by DB from SQL 2008 to SQL 2012 and all the stored procedures (more than 200) were returned with around 5 carriage returns after every line of code when using sp_helptext.

The above code helped me resolve it.

blocks|key|224511|text|我也为了一个观点而面对这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|224512|下面是我用来解决这个问题的步骤(请注意，这只是一个临时修复)|224513|1)选择查询|224514|2)然后右键单击并在查询设计器中打开|224515|3)点击确定|224516|你会注意到所有多余的空格都被删除了。|224517|注意:这只适用于简单的查询，而不适用于过程等|224518|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|S|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|V|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|O|$]]

I too faced this issue for a view.
Below are steps i used to resolve the issue (note only a temporary fix)
1)select the query
2)then right click and open in query designer
3)click ok
you would notice all the extra spaces are removed.
Note: this works only for simple queries not for procedures,etc

blocks|key|224528|text|有两种解决方案可用于从sp_helptext输出中删除额外空间|type|unstyled|depth|inlineStyleRanges|entityRanges|data|224529|224530|Goto+Tools>Option>Query+Results>Result+to+text+(从下拉列表中)|ordered-list-item|224531|Copy+sp_helptext+output+Paste+in+Notepad%2B%2B。通过按cntl%2BH打开“查找和替换”窗口，输入要替换为给定图片中的新文本的以下文本。enter+image+description+here|offset|length|224532|224533|entityMap|0|LINK|mutability|MUTABLE|url|https://i.stack.imgur.com/YWJ31.jpg^0|0|0|0|2D|S|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|T|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|V|8|@]|9|@$H|W|I|X|1|Y]]|A|$]]|$1|J|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

There are two type of solution which you can use for remove extra space from sp_helptext output

<ol>
<li>Goto Tools>Option>Query Results>Result to text (From drop down list)</li>
<li>Copy sp_helptext output
Paste in Notepad++. open find and replace window by pressing cntl+H enter the following text to replace with new text as in given picture.
<a href="https://i.stack.imgur.com/YWJ31.jpg" rel="nofollow noreferrer">enter image description here</a></li>
</ol>

blocks|key|2877046|text|我一直在使用Rufo的解决方案，但我刚刚意识到，当数据库出现破折号时，它就会崩溃。示例：[ST-SALES]。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2877047|对于它的价值，这是一个基于Rufo的解决方案的更新答案。|2877048|ALTER+PROCEDURE+[dbo].[sp_helptext2]+(@ProcName+NVARCHAR(256))
AS
BEGIN
++DECLARE+@PROC_TABLE+TABLE+(X1++NVARCHAR(MAX))

++DECLARE+@Proc+NVARCHAR(MAX)
++DECLARE+@Procedure+NVARCHAR(MAX)
++DECLARE+@ProcLines+TABLE+(PLID+INT+IDENTITY(1,1),+Line+NVARCHAR(MAX))

++SELECT+@Procedure+=+'SELECT+DEFINITION+FROM+['%2Bdb_name()%2B'].SYS.SQL_MODULES+WHERE+OBJECT_ID+=+OBJECT_ID('''%2B@ProcName%2B''')'

++insert+into+@PROC_TABLE+(X1)
++++++++exec++(@Procedure)

++SELECT+@Proc=X1+from+@PROC_TABLE

++WHILE+CHARINDEX(CHAR(13)%2BCHAR(10),@Proc)+>+0
++BEGIN
++++++++INSERT+@ProcLines
++++++++SELECT+LEFT(@Proc,CHARINDEX(CHAR(13)%2BCHAR(10),@Proc)-1)
++++++++SELECT+@Proc+=+SUBSTRING(@Proc,CHARINDEX(CHAR(13)%2BCHAR(10),@Proc)%2B2,LEN(@Proc))
++END
++--*+inserts+last+line
++insert+@ProcLines+
++select+@Proc+;

++SELECT+Line+FROM+@ProcLines+ORDER+BY+PLID
END|code-block|syntax|javascript|2877049|entityMap^0|18|A|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

I've been using Rufo's solution, but I just realized it breaks when the database has a dash. Example: <code>[ST-SALES]</code>.

For what it's worth, this is an updated answer based on Rufo's solution.

<pre><code>ALTER PROCEDURE [dbo].[sp_helptext2] (@ProcName NVARCHAR(256))
AS
BEGIN
 DECLARE @PROC_TABLE TABLE (X1 NVARCHAR(MAX))

 DECLARE @Proc NVARCHAR(MAX)
 DECLARE @Procedure NVARCHAR(MAX)
 DECLARE @ProcLines TABLE (PLID INT IDENTITY(1,1), Line NVARCHAR(MAX))

 SELECT @Procedure = 'SELECT DEFINITION FROM ['+db_name()+'].SYS.SQL_MODULES WHERE OBJECT_ID = OBJECT_ID('''+@ProcName+''')'

 insert into @PROC_TABLE (X1)
 exec (@Procedure)

 SELECT @Proc=X1 from @PROC_TABLE

 WHILE CHARINDEX(CHAR(13)+CHAR(10),@Proc) &gt; 0
 BEGIN
 INSERT @ProcLines
 SELECT LEFT(@Proc,CHARINDEX(CHAR(13)+CHAR(10),@Proc)-1)
 SELECT @Proc = SUBSTRING(@Proc,CHARINDEX(CHAR(13)+CHAR(10),@Proc)+2,LEN(@Proc))
 END
 --* inserts last line
 insert @ProcLines 
 select @Proc ;

 SELECT Line FROM @ProcLines ORDER BY PLID
END
</code></pre>

blocks|key|3091053|text|使用以下查询可获得精确的SP查询，无需额外空格：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3091054|(SELECT++OBJECT_DEFINITION+(OBJECT_ID('SPname'+)))|code-block|syntax|javascript|3091055|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use the below query to get exact SP query without extra spaces:
<pre><code>(SELECT OBJECT_DEFINITION (OBJECT_ID('SPname' )))
</code></pre>

I am using SQL server 2012, &amp; always use <code>SP_HELPTEXT</code> to get my previously created Stored Procedures, In previous versions of SQL server there were no issues in this process but in 2012, My Stored Procedures come with extra lines, for example this is the procedure that I wrote

<pre><code>Create proc SP_Test
as
begin
 Select * 
 from table_ABC
end
</code></pre>

Now after using <code>SP_HELPTEXT</code> with this procedure (or any other procedure), I am getting this output

<pre><code>Create proc SP_Test

as

begin

 Select * 

 from table_ABC

end
</code></pre>

Do any one else also facing this problem or I am the only one on this planet to struggle with this issue ??
Does any body know how to solve this issue ??

Configuration of my SQL server is as follows (copied from Help -> About )

<pre><code>Microsoft SQL Server Management Studio 11.0.2100.60
Microsoft Analysis Services Client Tools 11.0.2100.60
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 6.0 
Microsoft Internet Explorer 9.0.8112.16421
Microsoft .NET Framework 4.0.30319.269
Operating System 6.1.7601
</code></pre>

Thanx in advance.

SQL server 2012 SP_HELPTEXT extra lines issue

SQL Server

数据库

我正在使用SQL server 2012，并且总是使用SP_HELPTEXT来获取我以前创建的存储过程，在以前的SQL server版本中，这个过程没有问题，但是在2012年，我的存储过程附带了额外的行，例如，这是我编写的过程。Create proc SP_Testasbegin Select *  from tabl...

问SQL server 2012 SP_HELPTEXT额外行问题
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL server 2012 SP_HELPTEXT额外行问题EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL server 2012 SP_HELPTEXT额外行问题
EN