Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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


More info:


Comments

Leave a comment on the original post [www.sqltact.com, opens in a new window]

Loading comments...