Restrictions and extensions of print and sp_helptext in SQL, printsp_helptext

Restrictions and extensions of print and sp_helptext in SQL, printsp_helptext

It is common to use dynamic SQL in SQL. Some complex calculations, or stored procedures, have a long code and may execute SQL statements multiple times. However, debugging the SQL statements of strings is painful, and it is hard to see what statements are running. Therefore, I often use the print command to print the statement before running to the screen, and then copy it to another window for debugging and modification, which makes it easier. However, this print command has some restrictions. In a single-byte character set, the maximum length of 8000 characters can be printed, but in a double-byte character set is 4000.

The following stored procedure can print complete information, regardless of the length limit. The Code is as follows:

IF OBJECT_ID(N'sp_print_all') IS NOT NULLBEGINDROP PROCEDURE sp_print_allENDGOCREATE PROCEDURE sp_print_all (@dynamic_sql NVARCHAR(MAX))ASDECLARE @start INTDECLARE @len INTSET @start = 1SET @len = 4000WHILE (@start < LEN(@dynamic_sql))BEGINPRINT SUBSTRING(@dynamic_sql, @start, @len)SET @start = @start + @lenENDPRINT SUBSTRING(@dynamic_sql, @start, @len)GO 

Code-1

There is also a storage sp_helptext that can be used to query stored procedures, functions, and other code. It is also convenient to use, but it also has length restrictions, and the printed format does not match the source code format. Instead of writing a custom stored procedure, the Code is as follows:

IF OBJECT_ID(N'sp_helptext_user') IS NOT NULLBEGINDROP PROCEDURE sp_helptext_userENDGOCREATE PROCEDURE sp_helptext_user(@obj_name NVARCHAR(200) = '')ASSET NOCOUNT ON;DECLARE @text NVARCHAR(MAX),@i INT,@text2 NVARCHAR(MAX),@db_name SYSNAME,@obj_id BIGINTSET @db_name = PARSENAME(@obj_name ,3)IF @db_name IS NULLSET @db_name = DB_NAME()ELSE IF @db_name <> DB_NAME()BEGINRAISERROR(15250 ,-1 ,-1)RETURN (1)ENDSET @obj_id = OBJECT_ID(@obj_name)IF @obj_id IS NULLBEGINRAISERROR(15009 ,-1 ,-1 ,@obj_name ,@db_name)RETURN (1)ENDSELECT @text = [definition]FROM sys.all_sql_modulesWHERE [object_id] = @obj_idWHILE LEN(@text) > 2000BEGINSET @i = CHARINDEX(CHAR(13) ,@text ,2000) SET @text2 = LEFT(@text ,@i)SET @text = SUBSTRING(@text ,@i + 2 ,LEN(@text)) PRINT @text2ENDPRINT @textSET NOCOUNT OFF;GO 

Code-2

Of course, there are multiple ways to view the source code, you can operate on SSMS, etc, depending on personal habits or convenient operations.

Sp_helptext

Displays the text of rules, default values, unencrypted stored procedures, user-defined functions, triggers, or views.

Syntax

Sp_helptext [@ objname =] 'name'

Parameters

[@ Objname =] 'name'

Object Name. The definition information of the object is displayed. The object must be in the current database. The data type of name is nvarchar (776), with no default value.

Return code value

0 (successful) or 1 (failed)

Note

Sp_helptext displays the text used to create an object in multiple rows. Each row contains 255 characters defined by Transact-SQL. These definitions only reside in the text of the syscomments table of the current database.

Permission

The execution permission is granted to the public role by default.

Example

The following example shows the text of the employee_insupd trigger, which is in the database pubs.

USE pubsEXEC sp_helptext 'employee_insupd'