sys.sql_modules vs. INFORMATION_SCHEMA.Routines

Consider the following two queries meant to search for a keyword in SQL programmability objects.

select s.name +'.' + o.name, o.type_desc, m.definition
from sys.sql_modules m
inner join sys.objects o on m.object_id = o.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
where definition like '%dataserver2%'
order by o.name

select r.SPECIFIC_SCHEMA + '.' + r.SPECIFIC_NAME, r.routine_type, r.ROUTINE_DEFINITION
from INFORMATION_SCHEMA.routines r
where ROUTINE_DEFINITION like '%dataserver2%'
order by ROUTINE_NAME 

While the INFORMATION_SCHEMA is ANSI compliant and therefore may be more familiar to Oracle or IBM folks, I recommend using sys.sql_modules instead.  

I'll throw a LEN(m.definition) and LEN(r.ROUTINE_DEFINITION) on those queries and show:

Same results out of the search, but ROUTINES returns the first 4000 characters, as opposed to the output of sys.sql_modules which is an nvarchar(max). 

What's most dangerous about that?  If you're searching for a string in your database objects and that string happens to be in line past the 4000-character mark, a query on INFORMATION_SCHEMA will fail to return the object as a match!

Consider these two queries on the SQL 2012 RTM ReportServer database:

select s.name +'.' + o.name, o.type_desc, m.definition
from sys.sql_modules m
inner join sys.objects o on m.object_id = o.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
where m.DEFINITION like '%            AND OwnerID = @OwnerID%'
order by o.name;

select r.SPECIFIC_SCHEMA + '.' + r.SPECIFIC_NAME, r.routine_type, r.ROUTINE_DEFINITION
from INFORMATION_SCHEMA.routines r
where r.ROUTINE_DEFINITION like '%            AND OwnerID = @OwnerID%'
order by ROUTINE_NAME; 

Here's the result sets:

Yikes!

Consider also this cryptic, foreboding warning all over MSDN:

I prefer to stick with my sys objects instead of INFORMATION_SCHEMA when searching code.

Oh, and sys.syscomments is deprecated.

More info at sys.sql_modules.