SQL server 2012 SP_HELPTEXT extra lines issue
A better workaround (compared to use Results to text) in my opinion is to create an sp_helptext2 storedproc as explained here:
http://sql-javier-villegas.blogspot.com/2012/08/a-workaround-for-sphelptext-bug-in-ssms.html
Note: that solution has a bug leaving out the last line if there is no new line at the end. Corrected T-SQL:
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) > 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
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:
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) > 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<>'')
SELECT Line FROM @ProcLines where Line<>'' ORDER BY PLID
END
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.
I can replicate this behaviour if I run sp_helptext
with Results to grid
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 sp_helptext
from previous editions, since this effect isn't displayed with standard grid result sets.
The simplest work-around will be to run sp_helptext
with Results to text
set (Query
-> Results to
> Results to text
, shortcut CTRL
+ T
.
You may need to increase the maximum number of characters per line in Results to text
to get the output you expect - Tools
> Options
> Query Results
> Results to text
- set "maximum number of characters displayed in each column" to the maximum value of 8192.