Skip to content

Latest commit

 

History

History
108 lines (78 loc) · 4.33 KB

sp-helptext-transact-sql.md

File metadata and controls

108 lines (78 loc) · 4.33 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sp_helptext (Transact-SQL)
Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined function, trigger, computed column, CHECK constraint, view, or system object.
markingmyname
maghan
randolphwest
05/15/2024
sql
system-objects
reference
sp_helptext
sp_helptext_TSQL
sp_helptext
TSQL
=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current

sp_helptext (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

Displays the definition of a user-defined rule, default, unencrypted [!INCLUDE tsql] stored procedure, user-defined [!INCLUDE tsql] function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_helptext
    [ @objname = ] N'objname'
    [ , [ @columnname = ] N'columnname' ]
[ ; ]

Arguments

[ @objname = ] N'objname'

The qualified or nonqualified name of a user-defined, schema-scoped object. @objname is nvarchar(776), with no default. 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.

[ @columnname = ] N'columnname'

The name of the computed column for which to display definition information. @columnname is sysname, with a default of NULL. The table that contains the column must be specified as @objname.

Return code values

0 (success) or 1 (failure).

Result set

Column name Data type Description
Text nvarchar(255) Object definition

Remarks

sp_helptext displays the definition that is used to create an object in multiple rows. Each row contains 255 characters of the [!INCLUDE tsql] definition. The definition resides in the definition column in the sys.sql_modules catalog view.

Note

The system stored procedure sp_helptext isn't supported in Azure Synapse Analytics. Instead, use OBJECT_DEFINITION system function or sys.sql_modules object catalog view for equivalent results.

Permissions

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.

Examples

A. Display the definition of a trigger

The following example displays the definition of the trigger dEmployee in the [!INCLUDE ssSampleDBobject] database.

USE AdventureWorks2022;
GO
EXEC sp_helptext 'HumanResources.dEmployee';
GO

B. Display the definition of a computed column

The following example displays the definition of the computed column TotalDue on the SalesOrderHeader table in the [!INCLUDE ssSampleDBobject] database.

USE AdventureWorks2022;
GO
sp_helptext
    @objname = N'AdventureWorks2022.Sales.SalesOrderHeader',
    @columnname = TotalDue;
GO

[!INCLUDE ssResult]

Text
---------------------------------------------------------------------
(isnull(([SubTotal]+[TaxAmt])+[Freight],(0)))

Related content