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