SQL Server storage process sp_helptext deficiency and Solution

The system table SYS. syscomments and System View SYS. SQL _modules are introduced before sp_helptext.

SYS. syscomments: Contains each view, rule, default value, trigger, check constraint, default constraint, and stored procedure in the database.TextThe column contains the original SQL Definition Statement. (To put it simply, this system table stores the source code of the stored procedure and view we have created. By querying the system table, you can view the source code of the created stored procedure. SQL Server2000 introduced)

SYS. SQL _modules: returns a row for each module object defined in the SQL language. Objects of the P, RF, V, TR, FN, if, TF, and r types are associated with SQL modules. In this view, the independent default value, that is, the D type object also has the SQL module definition. (Introduced in SQL Server 2005)

The above two sentences are from the SQL server's local help document. When viewing the description of SYS. syscomments, there is an important prompt:

Click ing SQL Server 2000 system table to SQL Server 2005 System View and find:

This means that we recommend that you use the view SYS. SQL _modules instead of the SYS. syscomments system table.

Sp_helptext is a system stored procedure of ms SQL Server. You can use it to view the stored procedure or view source code (the best way is to bind the stored procedure by setting the shortcut key, which is more efficient ),

However, this stored procedure has the following Disadvantages:

1. It will help you format the code

2. When the length of a line exceeds a certain value, it will display it to your branch (this annoying problem comes when the line of code is too long)

This makes the Code look ugly .. I believe anyone who has used the sp_helptext stored procedure knows this problem.

Of course, you can right-click the stored procedure name and click Modify to display the source code. And the code format is intact. However, you cannot bind a shortcut key. Low efficiency.

Solution: Use SYS. syscomments or SYS. SQL _modules to write a stored procedure to display the source code, so that the format is not messy. You can also bind a shortcut key.

In view of Microsoft's important tips, use SYS. SQL _modules:

-- Siuon -- view the stored procedure source code create procedure mp_helptext (@ name varchar (255) asdeclare @ object_id int, @ sourcecode varchar (max), @ line varchar (max ), @ end int, @ rn varchar (2), @ tab varchar (1) Declare @ source table (source varchar (max) set @ Rn = char (13) + char (10) set @ tab = char (9) Select @ sourcecode = definition from sys. SQL _modules where object_id = object_id (@ name) while (charindex (@ RN, @ sourcecode )! = 0) beginset @ end = charindex (@ RN, @ sourcecode) set @ line = Replace (substring (@ sourcecode, 1, @ end-1), @ tab, @ tab + @ tab) if (charindex ('create', @ line) <> 0 and (charindex ('proc', @ line) <> 0 or charindex ('view', @ line) <> 0 or charindex ('function', @ line) <> 0 or charindex ('trigger', @ line) <> 0) beginset @ line = Replace (@ line, 'create', 'alter ') endinsert into @ source (source) values (@ line) set @ end = @ end + 2 set @ sourcecode = substring (@ sourcecode, @ end, Len (@ sourcecode) endinsert into @ source (source) values (@ sourcecode) select * From @ Source

Set the SQL Server shortcut key to bind the stored procedure:

SQL Server menu tool -- option -- Environment -- Keyboard: Here I set the CTRL + F1 key to the name of the stored procedure.

Click "OK" to restart SQL Server manage studio. You must restart it. Otherwise, it will not take effect.