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.

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

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.