pointers, solutions and scripts for the SQL DBA
not intended to replace common sense

4/18/2012

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:

1 comment:

  1. great article - thanks.

    just noticed that in the system procs are still using sys.syscomments - despite being deprecated in SQL 2012, can one take this deprecation seriously then?

    ReplyDelete

All comments are moderated before publishing. If you find something wrong or disagree, please comment so that this blog can be as accurate and helpful as possible.