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

PRINT object_definition(object_id('test'))

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

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, T-SQL