Home » sql server »sqlserverpedia-syndication »SSC »Tips »TSQL » Currently Reading:

New Hotness: sys.sql_modules

September 15, 2010 sql server, sqlserverpedia-syndication, SSC, Tips, TSQL 1 Comment

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

Currently there is "1 comment" on this Article:

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

Comment on this Article:







MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

September 13: SQL Saturday Kansas City
November 3-7: PASS Summit, Seattle, WADecember 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/