http://visakhm.blogspot.in/2012/03/advantages-of-using-syssqlmodules-view.html
There are two more related views available from SQL 2005 onwards. They’re sys.system_sql_modules and sys.all_sql_modules. So how are they different from sys.sql_modules?
The difference is as follows
sys.sql_modules – Will have a record for each user created modules in the database like procedure, view, trigger etc.
sys.system_sql_modules – Will have a record for each system modules in the database
sys.all_sql_modules – Will include the results of both the above views together i.e. it will have system as well as user modules in the current database listed
The advantages I see with these views is that they contain entire object definition included within definition column which is of BLOB type which we can query out using a simple select statement.
