Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.
sp_helptext [ @objname = ] 'name' [ , [ @columnname = ] computed_column_name ]
- [ @objname = ] 'name'
Is the qualified or nonqualified name of a user-defined, schema-scoped object. Quotation marks are required only if a qualified object is specified. If a fully qualified name, including a database name, is provided, the database name must be the name of the current database. The object must be in the current database. name is nvarchar(776), with no default.
- [ @columnname = ] 'computed_column_name'
Is the name of the computed column for which to display definition information. The table that contains the column must be specified as name. column_name is sysname, with no default.
Return Code Values
0 (success) or 1 (failure)
|Column name||Data type||Description|
sp_helptext displays the definition that is used to create an object in multiple rows. Each row contains 255 characters of the Transact-SQL definition. The definition resides in the definition column in the sys.sql_modules catalog view.
Requires membership in the public role. System object definitions are publicly visible. The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION.
A. Displaying the definition of a trigger
The following example displays the definition of the
uBillOfMaterials trigger in the
USE AdventureWorks; GO EXEC sp_helptext 'Production.uBillOfMaterials'; GO
B. Displaying the definition of a computed column
The following example displays the definition of the computed column
TotalDue on the
SalesOrderHeader table in the
USE AdventureWorks; GO sp_helptext @objname = N'AdventureWorks.Sales.SalesOrderHeader', @columnname = TotalDue ; GO
Here is the result set.
Text -------------------------------------------- (isnull(([SubTotal]+[TaxAmt])+[Freight],(0)))