How to extract SQL Server object definition in t-sql

With this post I’d like to share with you two ways of extracting the source text definition of a stored procedure, view, function, trigger, etc.

The first one is the metadata function OBJECT_DEFINITION(<object_id>). This function returns the source text definition in t-sql of the specified object. Objects that can be examined are stored procedures, views, rules, default and check constraints, triggers, functions and replication filter procedures. The use is as follows:

SELECT object_definition (object_id('sys.databases'))

The result returned is a single row transact sql statement.

However, OBJECT_DEFINITION function results are difficult to read or interpret so in help comes one stored procedure – sp_helptext <object_name>. This stored procedure is returning way more readable but has its limitations and differences – on the positive side there is the possibility to check definition of computed columns and DMVs (I am not sure how useful is that Smile – it’s geeky though!).

exec sp_helptext 'sys.databases'

[Edit 2011-07-02: adding one new method to the list]

After my original post I was told by a colleague of mine that there is one more way of extracting the t-sql definition of an object. That is by using sys.sql_modules. It is similar to OBJECT_DEFINITION metadata function except it is providing some more details about the object as is_schema_bound, execute_as_principal_id, uses_ansi_nulls, etc.

SELECT * FROM sys.sql_modules where object_id=object_id('sp_hexadecimal')

I have to say it is kind of more convenient than OBJECT_DEFINITION, but still sp_helptext is above all as it provides a structured output. Anyway there are cases where sp_helptext cannot be applied so you will have to use one of the other two methods.

4 Replies to “How to extract SQL Server object definition in t-sql”

    1. Hi there,
      I believe the output should be the same as long as the queried object falls in the scope of both sp_helptext and sys.sql_modules. Probably the formatting of the output could be different but the definition should not differ.

  1. Also the last solution is the only one that seams to work cross server through linked servers :)

Leave a Reply