locked
How to find all stored procedures which have something with specific table RRS feed

  • Question

  • I have tried to use SSMS find and find in files but they are very limited. Is there a way to search all procedures that have anything with any_table_name?
    Monday, August 12, 2013 2:05 PM

Answers

  • Hi, tnx for posting on MSDN forums.

    One way would be to use Information_Schema.Routines view. Here is an example

     
    select
     * 
    FROM  
    INFORMATION_SCHEMA.ROUTINES 
    WHERE 
    OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped')
     =0
    
          
    and OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_NAME))
    like
    '%search term here%'
    
          
    AND ROUTINE_TYPE='PROCEDURE'

     

    This solution has a drawback – it is limited to 4000 characters.

     

    My choice would be to use some third party tool, like  ApexSQL Search or SSMS Toolpack. They both are free. 
    Monday, August 12, 2013 2:27 PM

All replies

  • Hello Kameron,

    Please have a look at TechNet ScriptCenter: Search in SQL module definition

    With the script you can search in all SP / UDF etc definition for specific words.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, August 12, 2013 2:14 PM
    Answerer
  • Yes, you can use sys.sql_module catalog view. Try this one, please:

    USE AdventureWorks2008R2
    go
    
    -- all procs that contains "HumanResources" in their text
    
    SELECT  *
    FROM    sys.sql_modules m
            JOIN sys.objects o ON m.object_id = o.object_id
    WHERE   o.type = 'p'
            AND definition LIKE '%HumanResources%' ;


    If This post is helpful post, please vote it. If This post is the answer to your question, please Propose it as answer. Thanks so much for your feedback.

    • Proposed as answer by RohitGarg Tuesday, August 13, 2013 5:33 AM
    Monday, August 12, 2013 2:20 PM
  • Hi, tnx for posting on MSDN forums.

    One way would be to use Information_Schema.Routines view. Here is an example

     
    select
     * 
    FROM  
    INFORMATION_SCHEMA.ROUTINES 
    WHERE 
    OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped')
     =0
    
          
    and OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_NAME))
    like
    '%search term here%'
    
          
    AND ROUTINE_TYPE='PROCEDURE'

     

    This solution has a drawback – it is limited to 4000 characters.

     

    My choice would be to use some third party tool, like  ApexSQL Search or SSMS Toolpack. They both are free. 
    Monday, August 12, 2013 2:27 PM
  • In addition , I like using Aaron's great script  to looking for such things.

    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/06/a-handy-search-procedure.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Fanny Liu Tuesday, August 13, 2013 11:31 AM
    Tuesday, August 13, 2013 3:40 AM