sp_helptext vs object_definition()

3

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().

3 thoughts on “sp_helptext vs object_definition()

  1. 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

    • 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.

  2. SQLSoldier says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: