Home » SQL sp_helptext

SQL sp_helptext

SQL sp_helptext is a SQL Server System stored procedure that displays the the definition of any SQL Server objects.





You can see the definition of SQL stored procedure, user defined functions, computed column, trigger, CHECK constraint, view, or a system stored procedure.

It does not display the definition of encrypted object, If you have any encrypted SQL Stored procedure, or functions then it will not dispaly the definition of them.

To demonstrate this, first we Create a sample table named SalesByYear and Insert some records into this table and then create a Stored Procedure. that will return the Sales data from table SalesByYear.

CREATE TABLE dbo.SalesByYear
(
SalesId INT NOT NULL,
SalesExecutiveId INT NOT NULL,
Year INT NOT NULL,
Amount NUMERIC(9,2)
)
INSERT INTO dbo.SalesByYear
(SalesId, SalesExecutiveId, Year, Amount)
VALUES (1, 501, 1989, 1000),
(2, 501, 1990,2000),(3, 501, 2000, 3000),
(4, 501, 2001, 4500),(5, 501, 2002, 5000),
(6, 501, 2003, 6000),(7, 501, 2004, 3000),
(8, 501, 2005, 5500),(9, 501, 2006, 8500),
(10, 501, 2007, 9000),(11, 502, 1989, 1550),
(12, 502, 1990, 2500),(13, 502, 2000, 34000),
(14, 502, 2001, 4510),(15, 502, 2002, 5600),
(16, 502, 2003, 6500),(17, 502, 2004, 6000),
(18, 502, 2005, 7500),(19, 502, 2006, 8000),
(20, 502, 2007, 9600)

Lets create a stored procedure named as USP_FetchSales_ByYear.

CREATE PROC USP_FetchSales_ByYear
@Year INT
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM dbo.SalesByYear WHERE YEAR =@Year

END

Using Sp_helptext to see the definition of Stored Procedure

To see the definition of stored procedure USP_FetchSales_ByYear use following statement.

Sp_helptext USP_FetchSales_ByYear

A you can see, It returns the definition of stored procedure USP_FetchSales_ByYear.

Using sp_helptext to see the definition of SQL Server System stored procedure

You can also use Sp_helptext to see the definition of SQL Server system stored procedure as sp_helptext itself is a system stored procedure, Lets see the definition of sp_helptext using sp_helptext stored procedure.

Sp_helptext sp_helptext

As you can see, it returns the definition of sp_helptext system stored procedure.

 

Using sp_helptext to see the definition of user defined function

Lets create a simple user defined scalar functions that returns the sum for two input values.

CREATE FUNCTION Fn_Getsum( @A Int , @B Int) 

RETURNS  INT AS

BEGIN 

RETURN (@A + @B)

END

Lets see the definition of this function using sp_helptext.

Using sp_helptext to see the definition of computed column on table

Lets create a table named Stud having a computed column named BatchId.

CREATE TABLE dbo.Stud
(StudId INT NOT NULL,
NAME VARCHAR(50),
Age INT,
BatchId AS StudId
)

Now we have a table that have a computed column named BatchId, Lets see the definition of that column using sp_helptext.

sp_helptext N'Prod_Db.dbo.Stud', @columnname = BatchId




System Stored Procedure

SQL Server sp_rename

SQL Server sp_help

SQL Server sp_monitor

SQL Server sp_executesql

SQL Server Startup procedures Sp_Procoption

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 1,508 total views,  5 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.