Tuesday, October 14, 2014

sp_helptext line cuts' problem and simple workaround.

I really do not like to use Management Studio to generate a script of Stored  Procedure, Function, View or Trigger and for last several years dealing was almost constantly hit by "sp_helptext" problem.

The Problem.
The problem occurs when an object's text has lines which are bigger than 255 characters.
In that case "sp_helptext" automatically cuts the line, even if it is on the middle of a word.

Here is an example:
1. Create test Stored Procedure:


USE TestDB;
GO
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'CREATE PROCEDURE sp_Test_sp_helptext AS SELECT '''
+ REPLICATE('X',200)
+ ''' as [Stored Procedure Select Result];';
EXEC (@SQL);
GO

2. Try to extract body of the Stored Procedure by "sp_helptext":


EXEC sp_helptext 'sp_Test_sp_helptext';
GO

As a result you get two lines of code, which you won't be able to use out of the box because "sp_helptext" splits column name right in the middle. Just look at the second line:
"tored Procedure Select Result];"

If you try to re-create this SP you'll get an error. In order to fix it you'd have to fix line split manually.

Why I call that "sp_helptext" behavior as a "problem", because it is intentional behavior and not a bug.
See MSDN: http://msdn.microsoft.com/en-us/library/ms176112.aspx

The Solution.
The easiest solution is to write your code with 255 character limitation in mind or use only SSMS GUI to extract that code back to you.

However, I think you are here because you do not like "the easy way" and here is another easy solution to extract SQL code, which has lines of code longer than 255 chars.

Script below extracts code of our earlier created procedure correctly, without line separation:



DECLARE @objname nvarchar(776) = N'sp_Test_sp_helptext';

DECLARE @ObjectText nvarchar(MAX)='';
DECLARE @SyscomText  nvarchar(MAX);
DECLARE @LineLen INT;
DECLARE @LineEnd BIT = 0;
DECLARE @CommentText TABLE(
       LineId int IDENTITY(1,1),
       Text nvarchar(MAX) collate catalog_default);

DECLARE ms_crs_syscom  CURSOR LOCAL FOR
SELECT text FROM sys.syscomments
WHERE id = OBJECT_ID(@objname) and encrypted = 0
ORDER BY number, colid
FOR READ ONLY

OPEN ms_crs_syscom
FETCH NEXT from ms_crs_syscom into @SyscomText

WHILE @@fetch_status >= 0
BEGIN
       SET @LineLen = CHARINDEX(CHAR(10),@SyscomText);
       WHILE @LineLen > 0
       BEGIN

              SELECT @ObjectText += LEFT(@SyscomText,@LineLen)
                     ,      @SyscomText = SUBSTRING(@SyscomText, @LineLen+1, 4000)
                     ,      @LineLen = CHARINDEX(CHAR(10),@SyscomText)
                     ,      @LineEnd = 1;
             
              INSERT INTO @CommentText(Text)
              VALUES (@ObjectText)

              SET @ObjectText = '';
       END

       IF @LineLen = 0
              SET @ObjectText += @SyscomText;
       ELSE
              SELECT @ObjectText = @SyscomText
                     ,      @LineLen = 0;

       FETCH NEXT from ms_crs_syscom into @SyscomText
END

CLOSE  ms_crs_syscom;
DEALLOCATE    ms_crs_syscom;

INSERT INTO @CommentText(Text)
SELECT @ObjectText;

SELECT text FROM @CommentText
ORDER BY LineId;
GO


The Disclosure.
That code has been tested with code lines bigger than 255, 4000 and 8000 characters, but I wouldn't recommend to replace any section of "sp_helptext" in your production box because it might work incorrectly with types of objects different than simple Stored  Procedures, Functions, Views or Triggers.

5 comments:

  1. USE master

    DECLARE @ThisSchema NVARCHAR(128) = 'sys'
    , @ThisObject NVARCHAR(128) = 'sp_vupgrade_mergetabless'
    --, @CommentText NVARCHAR(MAX)

    SELECT definition
    FROM sys.all_sql_modules
    WHERE OBJECT_SCHEMA_NAME(object_id) = @ThisSchema
    AND OBJECT_NAME(object_id) = @ThisObject

    ReplyDelete
  2. Typo: 'sp_vupgrade_mergetabless' should be 'sp_vupgrade_mergetables'

    Also, if you are using SSMS query window to test, be sure to set the text size for the results to at least 116,367 characters (under Tools | Options | Query Results)

    ReplyDelete
    Replies
    1. Bill,

      Thanks for your comment.
      I like that solution.
      That is definitely more elegant extraction of SQL Code and in most cases that would work.

      However, I couldn't correctly extract a code of "sys.sp_vupgrade_mergetables" in your sample using SSMS.
      For the Text mode there is a hard limit of 8K and the hard limit for the Grid is 65K.
      Adjusted to a bug, you can get only 43679 characters for that procedure out of 116367 (as you noted).

      If you know the way how to make SSMS to show all 116367 characters please share it.

      Delete
  3. I forgot about the SSMS' text size limitation :).

    An SSMS workaround is to send Results to Grid (Ctrl+D), right-click the cell, and choose Copy.

    One SSMS-less solution is to pass -y0 to sqlcmd.exe. -y0 can exhaust system resources, which is also likely to be the reason why SSMS (outside of a grid) imposes a harsh limitation. I suspect the MS developer for text results did not interact with the MS developer for grid results (or, the same developer for both results was lazy :).

    ReplyDelete
    Replies
    1. Bill,

      With SSMS, even in Grid mode you are still limited by 43679 characters.

      That would be interesting to try sqlcmd.exe. I might go through it in one of my following posts.

      Delete