Nisarg Upadhyay
Get T-SQL definition of function using SSMS

An overview of the sp_helptext statement

June 28, 2021 by

In this article, we are learning about the sp_helptext statement. I am going to show the various examples of the usage of it. I will also explain the two alternatives of the sp_helptext statement.

The sp_helptext statement is used to view the definition of the following database objects.

Object type

Objects

Views

The T-SQL query used to create a view

Programmability

Functions, triggers, and stored procedures

Columns and constraints

CHECK Constraints, Computed columns

The syntax is following:

In the syntax,

  1. @obj_name: Specify the valid database object name. The object can be a stored procedure, function, computed columns, or triggers. If you are specifying the qualified object name (schema_name.object_name), then the quotes are required. The parameter does not have a default value
  2. @column_name: Specify the computed column name. The statement returns the T-SQL query that is used to create the computed column

When we run the sp_helptext statement, it displays the object definition in multiple rows, and each row has 255 characters of T-SQL definition. The definition of the database objects resides in the definition column of the sys.sql_modules DMV.

Now, let us understand the syntax with some examples. I am using the Wideworldimportors database.

Example 1: Display the definition of the stored procedure

To view the definition of the Website.ChangePassword stored procedure executes the following query.

T-SQL definition of Procedure

Example 2: Display the definition of the user-defined function

A user-defined, scalar function named named [Website].[CalculateCustomerPrice] has been created in the wideworldimportors database. To populate the definition of the [Website].[CalculateCustomerPrice], run the following query. I have trimmed the output screenshot because the T-SQL definition is very lengthy.

Trimmed Screenshot:

T-SQL definition of function

Example 3: Display the definition of the database view

A view named [Website].[Suppliers] has been created in the wideworldimportors database. To populate the definition of the [Website].[Suppliers], run the following query.

T-SQL definition of view

Example 4: Display the definition of the computed column

A computed column named SearchName has been created on the Application.People table. To populate the definition of the computed column, run the following query.

T-SQL definition of computed column

The above examples help you to generate a T-SQL script of the database objects. Now, let us take a look at the alternatives of the sp_helptext statement.

The first alternative of sp_helptext

We can use the SQL Server Management Studio (SSMS) to generate the script of the database object. We will learn how to generate a CREATE OBJECT script of the stored procedure, function, check constraint and computed columns.

Generate a T-SQL definition of the stored procedure

To generate the T-SQL definition of the Website.ChangePassword stored procedure, Open SSMS and connect to the database engine Expand Wideworldimportors database expand programmability right-click on Website.ChangePassword Hover on Script Stored Procedure as Hover on Create To Click on New Query Editor Window.

Get T-SQL definition of Procedure using SSMS

The T-SQL definition to create a stored procedure will be opened in a new tab.

T-SQL definition of Procedure using SSMS

  • Note: You can choose to copy the T-SQL script to the clipboard or save it to different files

Generate a T-SQL definition of the user-defined function

The steps to generate the T-SQL definition of the function are the same as the steps used in the stored procedure. To generate the definition, expand programmability expand functions expand user-defined function right-click on Website.CalculateCustomerPrice Hover on Script Function as Hover on Create To Click New Query Editor Window.

Get T-SQL definition of function using SSMS

The T-SQL script to create the function will be generated in a new tab.

T-SQL definition of Procedure using SSMS

Generate a T-SQL definition of the view

To generate the definition of the [Website].[Suppliers] view, Expand Wideworldimportors database expand views Right-click on [Website].[Suppliers] Hover on Script view as hover on Create To New query editor window.

Get T-SQL definition of view using SSMS

A T-SQL script to create a [Website].[Suppliers] view will be created in a new tab.

T-SQL definition of view using SSMS

Generate a T-SQL definition of the CHECK Constraint

The steps to generate the T-SQL definition of the check constraint are different. To generate the definition of the check constraint named [CK_Sales_Invoices_ReturnedDeliveryData_Must_Be_Valid_JSON] created on [Sales].[Invoices] table, Expand Wideworldimportors Expand Tables Expand Sales.Invoices Expand Constraints Right-click on [CK_Sales_Invoices_ReturnedDeliveryData_Must_Be_Valid_JSON] Script constraint as Create To New Query Editor Window.

Get T-SQL definition of Check constraint using SSMS

A T-SQL definition to create the constraint will be generated in the new tab of the query editor window.

The second alternative of the sp_helptext statement

As I mentioned at the beginning of the article, the helptext statement populates the definition of the object from the sys.sql_modules, so we can run the SELECT statement on the DMV to populate the T-SQL definition. Suppose you want to populate the T-SQL query of the Website.ChangePassword stored procedure. Run the following query.

Get T-SQL definition of view using Query.

Summary

In this article, we learned about the sp_helptext system stored procedure and its usage with several practical examples. Also, I have explained two alternatives to the helptext. The first alternative is to generate a script using SQL Server Management Studio. The second alternative is to run a SELECT statement on the sys.sql_modules table and populate the definition column.

Nisarg Upadhyay
721 Views