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.
