SSMS 2012–SP_HelpText workaround

 

There is a well-known issue in SQL Server Management Studio 2012 with sp_helptext: It puts extra lines in grid result set. This problem is known by the Microsoft since 2011 and it seems that they really do  not want to fix it until the next version of SQL Server.

I will show how to reproduce the problem and how to make a workaround for it.

I: Reproduce the problem:

1: Open SQL Server Management Studio 2012

2: Create a sample stored procedure:

DoSomething

1. create procedure dbo.DoSomething (
2.     @string nvarchar(100)
3. )
4. as begin
5.     set nocount on
6.  
7.     if @string is null or @string = ''
8.         return 1
9.  
10.     select @string
11. end
12. go

3: Run SP_HelpText with Results to Grid mode:

- Make sure that Results to Grid option is set:

- Run sp_helptext:

exec usp_helptext 'usp_helptext'

- Copy the resultset, and paste into the code window:

As we can se, as soon as we pasted the result into our code window, each line got an extra row.

 

II. Possible solutions:

- Most famous on the internet: use the Results to Text (CTRL-T) option and copy from there.

- Create our own version from the sp_helptext stored procedure.

- Switch to SSMS 2008R2

- Wait until the next version of SQL Server is released.

* Extra solution: use regexp replace on the copied result (we just have to replace all \n\n patterns to \n

I think there is no really good solution for the problem: Using Results to Text option means that we always have to switch between the text and grid mode during our work. As a database engineer I think that is only waste of time. We can create an own version of sp_helptext. The advantage is that we can use it really easy and we have a full control on the solution (that is why I have chosen this method. The disadvantage is that we have to compile our custom procedure in each database that we use. Of course we can switch to the older version of Management Studio too but I am not sure backward upgrading can be a proper solution for anything, and of course we can wait until the next release but if we work with it every day we must have a fix as soon as possible. And of course, we can use regexp replace but who has such time?

 

III. Let se usp_helptext

After I decided to create a custom version, I sat down and created the following script:

usp_helptext

1. drop procedure dbo.usp_helptext
2. go
3.  
4. create procedure dbo.usp_helptext(
5.   @SPName sysname
6. )
7. as begin
8.   set nocount on
9.  
10.   declare @LocalSPName varchar(100)
11.   declare @Definition nvarchar(max)
12.   declare @NewLine nchar(2) = CHAR(13) + CHAR(10)
13.   declare @SPDefTable table (RowNumber int identity(1,1) primary key, Row nvarchar(500) not null)
14.   declare @NLPos int
15.   declare @Row nvarchar(max)
16.   declare @Length int
17.  
18.   if @SPName is null or @SPName = ''
19.     return
20.  
21.   set @LocalSPName = @SPName
22.   select @Definition = definition from sys.all_sql_modules with(nolock) where object_id = object_id(@LocalSPName)
23.  
24.   if @@ROWCOUNT = 0
25.     return
26.  
27.   set @Length = LEN(@Definition)
28.  
29.   while @Definition > ''
30.   begin
31.     set @NLPos = PATINDEX('%' + @NewLine + '%', @Definition)
32.  
33.     if @NLPos <> 0
34.     begin
35.       set @Row = LEFT(@Definition, @NLPos - 1)
36.       set @Definition = SUBSTRING(@Definition, @NLPos + 2, @Length)
37.     end
38.     else
39.     begin
40.       break;
41.     end
42.  
43.     insert into @SPDefTable (Row)
44.     select @Row
45.   end
46.  
47.   if @Definition > ''
48.     insert into @SPDefTable (Row)
49.     select @Definition
50.  
51.   select Row
52.   from @SPDefTable
53.   order by RowNumber
54. end
55. go

As we can see, I have to learn a lot about programming sql server. This solution is based on my structured programming studies: It uses a loop and conditional statements. I made so of course it works fine but it is not a good-looking solution if your solution is set-based. It must contain some selects or it not a solution .
When I have finished with my usp_helptext procedure, I showed it to my boss. He likes challenges so immediately started to type something. When he finished, he send his solution to me and I started to test it. After I recognized all the boundary cases the transact-sql solution has born:

usp_helptext

1. drop procedure dbo.usp_helptext
2. go
3.  
4. create procedure dbo.usp_helptext(
5.   @SPName varchar(100)
6. )
7. as begin
8.   set nocount on
9.  
10.   declare @LocalSPName varchar(100)
11.   declare @Definition nvarchar(max)
12.   declare @NewLine nchar(2) = CHAR(13) + CHAR(10)
13.   declare @Length int
14.   declare @ErrMsg nvarchar(200)
15.  
16.   if @SPName is null or @SPName = ''
17.     return
18.  
19.   set @LocalSPName = @SPName
20.   select @Definition = definition from sys.all_sql_modules with(nolock) where object_id = object_id(@LocalSPName)
21.  
22.   if @@ROWCOUNT = 0
23.   begin
24.     set @ErrMsg = N'Cannot find stored procedure: ' + @LocalSPName
25.  
26.     raiserror (@ErrMsg, 16, 1)
27.   end
28.  
29.   set @Length = LEN(@Definition)
30.  
31.   ;with a as
32.   (
33.     select cast(1 as bigint) start, PATINDEX('%' + @NewLine + '%', @Definition) curlength, 0 as stopthere
34.     union all
35.     select a.start+a.curlength, PATINDEX('%' + @NewLine + '%', SUBSTRING(@Definition, a.start+a.curlength, @Length)) , case when PATINDEX('%' + @NewLine + '%', SUBSTRING(@Definition, a.start+a.curlength, @Length)) = 0 then 1 else 0 end
36.     from a
37.     where a.start+a.curlength < @Length and a.stopthere = 0
38.   )
39.   select REPLACE(REPLACE(SUBSTRING(@Definition, a.start, a.curlength), CHAR(10), ''), CHAR(13), '')
40.   from a
41.   option (maxrecursion 0)
42.  
43. end
44. go

I am proud to this solution. It is really a set-based solution and it shows what is the difference between an usual programmer and an sql developer. The golden rule is: If we want to use a loop we just have to sit down and search a proper select statement.

The real multi-lingual site

We can find hungarian, english and german languages in one form.