sp_helptext

If you need to see the definition of an object you have several choices.  First, you could right click on the object and generate the script.  Another option you have is to use sp_helptext.  This system stored procedure can be used on any version of SQL Server since 2008, this includes Azure SQL Database and Azure SQL Data Warehouse.  This stored procedure can be used to obtain the definition of a number of objects, however it does not work on all object types.

According to the Microsoft documentation you can use this on the following object types:

        • User-defined rule
        • Default
        • Unencrypted Transact-SQL stored procedure
        • User-defined Transact-SQL function
        • Trigger
        • Computed column
        • CHECK constraint
        • View
        • System object such as a system stored procedure

The procedure has two parameters, @objname and @columnname.  The first parameter, @objname is the name of the object you are seeking to get the definition.  The second parameter, which is optional,  can be used to get the definition of a computed column.

When using the stored procedure you must have the rights to view definitions of objects.

We will start with the obvious, the first parameter, the name of the object.  There just a few rules regarding this parameter.  First of all, the object must exist in the current database.  If the object is not in the dbo schema, you will need to include the schema and include single quotes like below.

sp_helptextsales.SalesOrderDetail

If you include the schema in the object name and do not add the quotes you will get following error.

sp_helptext dbo.uspGetBillOfMaterials

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘.’.

If the object is in the dbo scheme it will look like this, single quotes not needed.  Although quotes are not required, it will also not fail if you include them.

sp_helptext uspGetBillOfMaterials

When you execute the above statement you will get these results.  Sometimes it might be nice to see it in the grid, however I find it better to send the results to text instead.  It is easier to copy to the clipboard.

When you send the results to text, SQL Server will add the word text at the top, this is actually the column name of the result set.

 

I really like to use sp_helptext when I have multiple objects that I need script. However, there is a bit of a catch.  If you try to run it without the GO keyword between you will receive this error.

The second parameter only works on calculated columns.  Below you will find the code.  Notice that the column name is not in quotes.  It will work either way, with or without quotes.

sp_helptext @objname = N’Sales.SalesOrderHeader‘, @columnname = salesordernumber ;

This is the results of the above statement:

(isnull(N’SO’+CONVERT([nvarchar](23),[SalesOrderID]),N’*** ERROR ***’))

If you attempt to run this against a column that is not a calculated column you will get this error:

Msg 15646, Level 16, State 1, Procedure sp_helptext, Line 75 [Batch Start Line 0]
Column ‘orderdate’ is not a computed column.

Just a few additional comments.  Sp_helptext will not work on encrypted objects.

This next topic may be in a future blog post, however, I did want to mention there as well.  There is another option. This code will return the definition of the uAddress trigger.

USE AdventureWorks2014;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID(N‘Person.uAddress’)) AS ‘Trigger Definition’;
GO

When you run this, you will see the definition of the trigger.

Thank you for visiting my blog.