sys.sql_modules was a new catalog view which got introduced from SQL 2005 onwards. I’ve used it quite a lot in my day to day work. Just thought of sharing some of reasons why its preferred over syscomments view
a. Finding object dependencies on a table/column
Though we’ve sp_depends view which gives us details on dependency of an object like table,view etc it wont return details of any dependency which involves usage of dynamic sql ie for ex cases where table was used in a dynamic sql code inside procedure. In such cases best way to get those dependencies is through a query using sql_modules like
select object_name(object_id) from sys.sql_modules where definition like ‘% ‘ + @theobjectname + ‘%’
Till sql 2005, I was using syscomments system table for this purpose. Though it also comes handy in similar situations, there are small things to note in using this. They are:-
1. It wont return UDF information.
2. The text field which stores definition of object is limited to 4000 characters in syscomments. So if definition of object exceeds 4000 characters it spans multiple rows. In comparison, the definition column in sys.sql_modules is of BLOB type (nvarchar(max)) so can store definition in a single row itself.
b. Programmatically scripting out objects from a db
Again since it has definitions of all common object types stored like procedure,UDF,trigger etc its an ideal candidate to use if you want to programatically script out similar objects from db even based on some pattern. Here also since you’ve entire definition stored in same row you can very easily retrieve the result as compared to syscomments which requires definition to be merged from multiple rows if its over 4000 characters
For the above reasons I prefer using sql_modules over syscomments especially in above two scenarios discusse and it seems to be a prefect replacement for syscomments so far
