How To Use sp_helptext And sp_help Functionality In Mssql

Hello Friend Today In This Blog I Will Show You How To Use sp_helptext And sp_help Functionality In Mssql.This is another popular question I receive. How to see text/content/code of Stored Procedure. System stored procedure that prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.This procedure returns the T-SQL code used to define a programmable object, such as a default, rule, view, stored procedure, trigger or a user-defined function. This information is obtained from the syscomments system table found in every database, however, you can only retrieve the code for objects in the current database. Also note that if you use the WITH ENCRYPTION clause when creating your programmable objects, you won't be able to use sp_helptext to retrieve the code. You will not receive any errors when executing sp_helptext against encrypted object, but you will get the encrypted code, which you won't be able to read.SQL Server Query Analyzer as well as the Enterprise Manager offer fine tools to examine the source code of your programmable objects. In Query Analyzer you can simply right click on a stored procedure and choose 'Script Object to New Window As'. From that option you have several choices: you can script your procedure with a CREATE PROC, ALTER PROC or EXEC statement. The Query Analyzer will go one step further if your procedure has parameters and you choose to script it with EXEC - it will automatically declare variables with appropriate data types for you. All you have to do is set those variables to appropriate values and execute the procedure. In Enterprise Manager you can right click on the stored procedure and choose properties. Alternatively you could right click on any object, choose 'all tasks',  'generate SQL script' and script any or all of your database objects.I guess we've all scripted objects out of our database one way or another. We've scripted stuff out of SSMS or using sp_helptext, Object_definition or a tool like Redgate's SQL Prompt - my go to tool for this sort of thing. However, if you're using sp_helptext then you should be aware of the limit of 255 characters on each row as it could have some very interesting effects on your scripts.Be very careful when scripting using sp_helptext as it may shift a fully function sql statement on to a new line that completely changes your object. Granted you have to be very unlucky, but it is definitely possible! Most times of course you will find that your new script doesn't parse and won't run. Of the pros and cons of different scripting techniques this is the worst con I've found so far. I think the fact that it is a microsoft system stored procedure means that some users will think the script is guaranteed to be what is actually running on the server.

A stored procedure allows modular programming.

You can create the procedure once, store it in the database, and call it any number of times in your program.

A stored procedure allows faster execution.

If the operation requires a large amount of SQL code that is performed repetitively, stored procedures can be faster. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in a memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use, resulting in much faster execution times.

Syntax

sp_helptext objname[,grouping_num][, numlines[, printopts]]]

Sp_help syntax:

sp_help [ @objname = 'Object name.' ] ;

Step 1 :Create database

Create database Youtube

Step 2:Create table

Here In This step We Will Create table.

Here In This Step We Required Four Column With Name vid,vname,vlocation and vpassword using Parameter int,varchar.


Create table viewer 

(

vid bigint,

vname varchar(50),

vpassword varchar(50),

vlocattion varchar(50)

)

Step 3:insert data using store procedure

Here In This Step We Will Create Insert Procedure By Passing Four Parameter By Using Insert Trigger Functionality. 

create procedure wb_insert_user

(

@vid bigint,

@vname varchar(50),

@vpassword varchar(50),

@vlocattion varchar(50)

)

as

begin

insert into viewer(vid,vname,vpassword,vlocattion)

values(@vid,@vname,EncryptByPassPhrase('Tutorial',@vpassword),@vlocattion)

end

Step 4:view data using store procedure

Here In This Step We Will Create View Procedure Using Select Trigger Functionality. 

create procedure wb_view_user

as

begin

select 

ISNULL(vid,'-')vid,

ISNULL(vname,'-')vname,

ISNULL(CONVERT(varchar(50),DecryptByPassphrase('Tutorial',vpassword)),'-')vpassword,

ISNULL(vlocattion,'-')vlocattion

from viewer

end

Step 5:update data using store procedure

Here In This Step We Will Create Update Procedure By Passing Four Parameter By Using Update Trigger Functionality. 

create procedure wb_update_user

(

@vid bigint,

@vname varchar(50),

@vpassword varchar(50),

@vlocattion varchar(50)

)

as

begin

update viewer

set

vid=@vid,

vname=@vname,

vpassword=EncryptByPassPhrase('Tutorial',@vpassword),

vlocattion=@vlocattion

where vid=@vid

end

Step 6:delete data using store procedure

Here In This Step We Will Create Delete Procedure By Passing oneParameter By Using Delete Trigger Functionality.

create procedure wb_delete_user

(

@vid bigint

)

as

begin

delete from viewer

where vid=@vid

end

Step 7:Use sp_helptext And sp_help

In this Step Use Of sp_helptext And sp_help Will Be show Below.

sp_helptext wb_insert_user

sp_help wb_insert_user

Video Related To This Topic Is Here Below.

HOW TO USE SP_HELPTEXT AND SP_HELP FUNCTIONALITY IN SQL SERVER