New Hotness: sys.sql_modules

I’m still old school in many ways. And they’re not the ways that let you get away with wearing wayfarers, or shouting “get off my lawn, you kids!”

In this case, I’m still havin a hard time weaning myself off the old system table “syscomments”. I LOVE syscomments…I can find a piece of code anywhere in the database simply with 

FROM syscomments

Yes, old habits die hard, but syscomments really is the old and busted (and soon to be deprecated). So, let me reintroduce sys.sql_modules!

, [Definition]
FROM sys.sql_modules
WHERE [Definition] LIKE '%2008%'

Find all the instances of NOLOCK in your database with one simple query! Forgot where you wrote that string of swears in the comments? Just search on ‘%D____CH____G%’ (or your chosen swear) in sys_modules!

Oh yeah, and why aren’t you watching 24 Hours of PASS right now?

Happy learning,



About Jen McCown

This is my site. Details inside.

1 Comment

  1. […] Last month we talked about the old and busted syscomments- and the new hotness that is sys.sql_modules – for pulling code out of your database using T-SQL.  I’m finally transitioning over to sql_modules for good, even though I’d been a slow adopter.  This month, though, I’m already in SQLlove with the new hotness: sys.objects. […]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>