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:
|
1 |
exec sp_helptext [ @obj_name = ] 'Object_Name' , [ @column_name = ] computed_column_name |
In the syntax,
- @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
- @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.
|
1 2 3 |
use WideWorldImporters Go exec sp_helptext 'Website.ChangePassword' |
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.
|
1 2 3 |
use WideWorldImporters Go exec sp_helptext 'Website.CalculateCustomerPrice' |
Trimmed Screenshot:
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.
|
1 2 3 |
use WideWorldImporters Go exec sp_helptext 'Website.Suppliers' |
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.
|
1 2 3 |
use WideWorldImporters Go exec sp_helptext 'Application.people','SearchName' |
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.
The T-SQL definition to create a stored procedure will be opened in a new tab.
- 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.
The T-SQL script to create the function will be generated in a new tab.
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.
A T-SQL script to create a [Website].[Suppliers] view will be created in a new tab.
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.
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.
|
1 2 3 |
use WideWorldImporters go select definition from sys.sql_modules where object_id=object_id('Website.ChangePassword') |
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.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022