默认的sp_helptext 如果存储过程每行代码太长会自动截断
把这个sp_helptextplus添加到SSMS的keyboard快捷键里面
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROC sp_HelpTextPlus @SPNAME NVARCHAR(255)
AS
BEGIN
DECLARE @objname NVARCHAR(776) = @SPNAME;
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;
END
GO