http://www.sqlservercentral.com/blogs/sqltact/2012/04/18/syssql_modules-vs-information_schemaroutines/

Printed 2014/10/01 03:38AM

sys.sql_modules vs. INFORMATION_SCHEMA.Routines

2012/04/18

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 
And here's the output:








Consider also this cryptic, foreboding warning all over MSDN:



And you'll see why I prefer to stick with my sys objects instead of INFORMATION_SCHEMA when searching code.

Oh, and sys.syscomments is deprecated.

More info:
http://msdn.microsoft.com/en-us/library/ms175081.aspx



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.