sp_helptext vs object_definition()

December 21, 2016 by Kenneth Fisher

I’ve been using sp_helptext for years. Honestly I probably end up using it at least once a day. But I was recently exposed to object_definition() which has a similar output. There are, however, a few differences.

sp_helptext

This system function returns the text of any (T-SQL) code based object. I.e. views, functions, stored procedures and triggers. It’s very simple to call and you just pass it the name of the object you are interested in. There are a few downsides, though. It uses the syscomments system view which has been deprecated. Although I’m not overly worried about that because sp_helptext hasn’t been deprecated. So if syscomments changes or goes away sp_helptext will have to be changed to still work the same way.

So how does it work? It outputs the code definition in a table 255 characters wide. Which has a couple of effects. First, it completely messes up formatting unless you output to text. Second, regardless of outputting to text or grid, any line of text in the code that is longer than 255 characters is going to be broken up into multiple lines. Which can be a real pain when it happens. Which hopefully isn’t all that often. 255 characters is a pretty long line in a piece of code.

object_definition()

object_definition() returns the definition of the following types of objects:

C = Check constraint
D = Default (constraint or stand-alone)
P = SQL stored procedure
FN = SQL scalar function
R = Rule
RF = Replication filter procedure
TR = SQL trigger (schema-scoped DML trigger, or DDL trigger at either the database or server scope)
IF = SQL inline table-valued function
TF = SQL table-valued function
V = View

You can see this is quite a few more object types than sp_helptext. It returns code where appropriate, but for something like a default constraint it returns the default value. It also returns the value as a single varchar(max) field so the formatting remains intact. Even better because this is a function you have several options when calling it. You can even use PRINT. Why does that matter?

exec sp_helptext test

object_definition1

PRINT object_definition(object_id('test'))

object_definition2

No header! It might not make a difference to you but it means I don’t have to mess with deleting or avoiding it.

It does require the object_id which is kind of a pain. All told it’s an extra ~20 characters to type. Not that big a deal.

Last but not least, because it returns the value in a single row it’s far easier to use in automation. Not that Powershell and SMO wouldn’t be easier still in most situations.

End result I’ll probably start transitioning from sp_helptext to object_definition().

Category: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: microsoft sql server, T-SQL

4 thoughts on “sp_helptext vs object_definition()”

richardldawson says:

December 21, 2016 at 10:31 AM

Great post! I have a question. Have you tried returning the results from sp_helpText to a ‘text’ window? You still get the header but it doesn’t mess up the formatting of the object.

Have a great day.
-RD

Kenneth Fisher says:

December 21, 2016 at 11:35 AM

Yep. And if you want to do multiple SPs you can do something like this
```
EXEC sp_helptext 'sp1';
PRINT 'GO';
EXEC sp_helptext 'sp2';
PRINT 'GO';
```
And just get rid of the headers to have a script for multiple SPs at once.

Reply

SQLSoldier says:

December 27, 2016 at 10:46 AM

Another option to consider: some of the current (as in not SQL 2000) system views have a definition column. So for example, you can get the objects definitions of all procedures in a single query if you so choose.

Select OBJECT_SCHEMA_NAME(object_id) + N’.’ + OBJECT_NAME(object_id)
From sys.all_columns
Where name = ‘definition’;

sys.computed_columns
sys.system_sql_modules
sys.check_constraints
sys.default_constraints
sys.numbered_procedures
sys.sql_modules
sys.server_sql_modules
sys.all_sql_modules
sys.syscharsets
sys.masked_columns