String search in Stored Procedures

I needed a way to get a list of all stored procedure that contained a TRUNCATE command. We were performing a security audit on one of our applications, trying to determine which users needed what access.

I stumbled on this article  from Pinal Dave’s blog and ended running this query:

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
where sc.TEXT LIKE ‘%TRUNCATE%’

Works like a charm. Also used it to find Stored procedures with DROP

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
where sc.TEXT LIKE ‘%DROP%’

Advertisements

3 thoughts on “String search in Stored Procedures

  1. In 2005 and above, check out sys.sql_modules as an alternative to syscomments – which doesn’t suffer from the potential pitfall of an objects definition being split across a 4000 character boundary.

    • Awesome will. Thanks for the tip. I was going to inform you that today I downloaded version 3 of SQLJobVis. Working good. Do you have some release notes or a features list?

      Thanks again!
      Joe

  2. No problem, Joe – most of the time it won’t matter – then there’ll be that one day when the sproc search is mission critical and hell and handbasket meet. 🙂 I must say I’ve used syscomments myself for years without having hit a problem, but as there’s a safer alternative best go with that.

    Glad to hear you’ve gotten your hands on the latest SQLjobvis – I hope you enjoy it! There aren’t any release notes at the moment – the real ‘features of note’ are the MDI look-and-feel – the saving / restoring of connections between sessions and more configurability through the Options dialog. You’ll find that the refresh is also asynchronous now – which is more useful when starting the application with many connections open (from the previous session) that when F5’ing any particular window.

    Whilst this is v3, I don’t think it’s v3 final yet – I’ve still a few things in the pipeline, so expect new features to appear in the near future!

    Best Wishes,

    Will.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s