DISQUS

DISQUS Hello! Tech-Recipes is using DISQUS, a powerful comment system, to manage its comments. Learn more.

Community Page

Tech-Recipes

Cookbook of Tech Tutorials
Jump to original thread »
Author

SQL Server 2005: Locating Stored Procedures that Contain a Keyword

Started by qdideas · 7 months ago

Making changes to a database that has been created by a third pary can be a difficult task, especially when you don't want to break any of the existing stored procedures. You can search the database's stored procedures for keywords to find which procedures use a keyword that is involved in yo ... Continue reading »

2 comments

  • sleakbug

    6 months ago

    In SQL 2000 you will need to join together all the 8K records in syscomments for each stored procedure to search within, and beware of your search term breaking across an 8K boundary between 8K records. That will require creating a record that includes the last 1000 chars of one record + the first 1000 chars of the next record to include in the search. You may use less than 1000 chars depending on the size of your search terms.
    reply
  • Dan Wygant

    5 months ago

    Thanks! good info. Here is the OBJECT_NAME built-in for SS05 and beyond... I'm on ss08 now.

    select OBJECT_NAME(m.object_id), m.definition from sys.sql_modules m where
    Definition like '%YourKeyword%'

    Frankly I like yours better
    reply

Add New Comment

Returning? Login