Limitations and extensions of print, sp_helptext

In SQL, it is common to use dynamic SQL. There are complex calculations, or stored procedures, where the code is long and there may be multiple execution of SQL statements in the middle. Debugging a bunch of SQL statements is a pain, and it's hard to see what the statements are running. So I will often use print this command to print the pre-run statements to the screen, and then copied out to another window for debugging, modification, so it is more convenient. However, this print command has some limitations, in the case of a single-byte set, the maximum print size is 8,000 characters, while the double-byte character set is 4,000.

The following stored procedures can print the complete information, not limited by the length. The code is as follows:

IF object_id(N'Sp_print_all') is  not NULLBEGIN    DROP PROCEDURESp_print_allENDGOCREATE PROCEDURESp_print_all (@dynamic_sql NVARCHAR(MAX)) asDECLARE @start INTDECLARE @len INTSET @start = 1SET @len = 4000 while(@start < LEN(@dynamic_sql))BEGIN    PRINT SUBSTRING(@dynamic_sql,@start,@len)    SET @start = @start + @lenENDPRINT SUBSTRING(@dynamic_sql,@start,@len)GO

Code-1

There is also a storage sp_helptext, can query stored procedures, functions and other code, the use of more convenient, but also has the length of the limit, and the printed format and the format of the source code is not corresponding. Write a custom stored procedure instead, the code is as follows:

IF object_id(N'Sp_helptext_user') is  not NULLBEGIN    DROP PROCEDURESp_helptext_userENDGOCREATE PROCEDURESp_helptext_user (@obj_name NVARCHAR( $)= "') asSETNOCOUNT on; DECLARE @text        NVARCHAR(MAX)           ,@i           INT           ,@text2       NVARCHAR(MAX)           ,@db_nameSYSNAME,@obj_id      BIGINT    SET @db_name = ParseName(@obj_name,3)    IF @db_name  is NULL        SET @db_name = db_name()    ELSE     IF @db_name <> db_name()    BEGIN        RAISERROR(15250,-1,-1)        RETURN(1)    END        SET @obj_id = object_id(@obj_name)    IF @obj_id  is NULL    BEGIN        RAISERROR(15009,-1,-1,@obj_name,@db_name)        RETURN(1)    END        SELECT @text = [definition]     fromSys.all_sql_modulesWHERE  [object_id] = @obj_id         while LEN(@text)>  -    BEGIN        SET @i = CHARINDEX(CHAR( -) ,@text, -)                SET @text2 =  Left(@text,@i)        SET @text = SUBSTRING(@text,@i + 2,LEN(@text))                PRINT @text2    END    PRINT @textSETNOCOUNTOFF;GO

Code-2

Of course, there are several ways to view the source code, you can operate on SSMs, and so on, looking at personal habits or convenient operation.

Limitations and extensions of print, sp_helptext