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 

SELECT OBJECT_NAME(id) ObjectName
, TEXT
FROM syscomments
WHERE TEXT LIKE '%2008%'

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!

SELECT OBJECT_NAME(OBJECT_ID) ObjectName
, [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,

Jen

http://www.MidnightDBA.com/Jen

One thought on “New Hotness: sys.sql_modules”

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>