MSSQL sp_helptextplus

时间:2021-09-21 09:54:26

默认的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