http://www.sqlservercentral.com/blogs/sql_awesomesauce/2010/09/15/new-hotness_3A00_-sys.sql_5F00_modules/

Printed 2014/12/29 12:30AM

New Hotness: sys.sql_modules

By Jen McCown, 2010/09/15

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


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