Three methods of getting sqlserver stored procedure definition

Time:2020-2-26

The concept of stored procedure

A stored procedure is a set of SQL statements that are compiled and stored in a database to complete a specific function. The user can execute it by specifying the name of the stored procedure and giving parameters.

Stored procedures can contain logical control statements and data manipulation statements, which can accept parameters, output parameters, return single or multiple result sets and return values.

Because stored procedures are compiled on the database server and stored in the database when they are created, stored procedures run faster than a single block of SQL statements. At the same time, because only the stored procedure name and necessary parameter information are needed in the call, the network traffic and simple network burden can be reduced to a certain extent.

Advantages of stored procedures

A. Stored procedures allow standard component programming

After a stored procedure is created, it can be called and executed multiple times in the program without having to rewrite the SQL statement of the stored procedure. And database professionals can modify stored procedures at any time, but have no impact on the application source code, which greatly improves the portability of the program.

B. Stored procedures can achieve faster execution speed

If an operation contains a large number of T-SQL statement codes and is executed multiple times, the execution speed of a stored procedure is much faster than that of a batch process. Because stored procedures are precompiled, when running a stored procedure for the first time, the query optimizer analyzes and optimizes it, and gives the storage plan that is finally stored in the system table. The batch T-SQL statements need to be precompiled and optimized every time they run, so the speed is slower.

C. Stored procedures reduce network traffic

For the same operation for database objects, if the T-SQL statement involved in this operation is organized into a stored procedure, when the stored procedure is called on the client, only the call statement will be passed in the network, otherwise multiple SQL statements will be passed. Thus, the network traffic is reduced and the network load is reduced.

D. Stored procedures can be used as a security mechanism to make full use of

The system administrator can restrict the permission of a stored procedure, which can restrict the access of some data, avoid the access of unauthorized users, and ensure the security of data.

The concept of stored procedure and the advantages of stored procedure introduced above are all to pave the way for this article, focusing on the three methods of SQL server stored procedure definition, let’s have a look!

The first is:

declare @p_text varchar(max)
SELECT @p_text= text 
FROM syscomments 
Where id = (select id from sysobjects where name = 'stored procedure name')
print @p_text

But when the content of stored procedure is long, the content of text is not completely displayed.

Second species:


declare @p_text varchar(max)
SELECT @p_text= definition FROM sys.sql_modules 
JOIN sys.objects ON sys.sql_modules.object_id=sys.objects.object_id --and type='P' 
and sys.objects.name='sp_MSupd_dboInd_IndependenceReportTo' 
print @p_text

The content format is good, but print itself can’t display more than 8000 bytes of content, so the stored procedure content is too long.

Third species:


exec sp_helptext '[sp_MSupd_dboRpt_Customer]'

Return the defined content but one text line and one record line, which can solve the above problems.

The above three methods are introduced by Xiaobian to get sqlserver stored procedure definition. I hope they can help you. If you have any questions, please leave me a message and Xiaobian will reply to you in time. Thank you very much for your support of the developepaer website!