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.
USE master
ReplyDeleteDECLARE @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
Typo: 'sp_vupgrade_mergetabless' should be 'sp_vupgrade_mergetables'
ReplyDeleteAlso, 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)
Bill,
DeleteThanks 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.
I forgot about the SSMS' text size limitation :).
ReplyDeleteAn 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 :).
Bill,
DeleteWith 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.