Shulou(Shulou.com)05/31 Report--

This article is to share with you about the restrictions and extensions of print and sp_helptext in SQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

In SQL, it is common to use dynamic SQL. Some complex calculations, or stored procedures, the code is very long, there may be multiple execution of SQL statements. However, debugging the SQL statement of the collage is painful, and it is difficult to see what the statement is running. So I often use the print command to print the pre-run statements to the screen, and then copy them to another window for debugging and modification, which is more convenient. However, this print command has some limitations. In a single-byte character set, the maximum print length is 8000 characters, while in a double-byte character set, it is 4000.

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

IF OBJECT_ID (nasty spares printmakers) 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 还有一个存储sp_helptext，可以查询存储过程，函数等代码，使用起来也比较方便，但也有长度的限制，而且打印出来的格式跟源代码的格式有些对应不上。写了一个自定义存储过程来代替，代码如下： 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 many ways to view the source code, you can operate on SSMS, etc., depending on personal habits or convenient operation.

Detailed explanation of sp_helptext

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

Grammar

Sp_helptext [@ objname =] 'name'

Parameters.

[@ objname =] 'name'

The name of the object that displays the definition information for that object. The object must be in the current database. The data type of name is nvarchar, and there is no default value.

Returns the code value

0 (success) or 1 (failure)

Annotation

Sp_helptext displays the text used to create the object in multiple lines, each with 255characters defined by Transact-SQL. These definitions reside only in the text of the syscomments table in the current database.

Authority

Execute permissions are granted to the public role by default.

Example

The following example shows the text of an employee_insupd trigger that is in the database pubs.

The above USE pubsEXEC sp_helptext 'employee_insupd' is the limitation and extension of print and sp_helptext in SQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.