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 Thoughts on “How to extract SQL Server object definition in t-sql

  1. DBA on Nov 18, 2011 at 01:26 said:

    Hi,
    Is it possible that the result of sp_helptext and sys.sql_modules has a diffrence.

    SQL DBA

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

  2. Pingback: How to extract SQL Server object definition in t-sql | Yet another SQL Server blog

  3. Bako Mite on Jun 3, 2013 at 16:23 said:

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

Leave a Reply

Post Navigation