Advertisement
Advertisement


Search text in stored procedure in SQL Server


Question

I want to search a text from all my database stored procedures. I use the below SQL:

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
  FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
 WHERE m.definition Like '%[ABD]%';

I want to search for [ABD] in all stored procedures including square brackets, but it's not giving the proper result. How can I change my query to achieve this?

2018/10/12
1
845
10/12/2018 10:19:34 AM

Accepted Answer

Escape the square brackets:

...
WHERE m.definition Like '%\[ABD\]%' ESCAPE '\'

Then the square brackets will be treated as a string literals not as wild cards.

2017/12/05
587
12/5/2017 1:31:06 PM

Try this request:

Query

SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%strHell%'
2016/12/16

Have you tried using some of the third party tools to do the search? There are several available out there that are free and that saved me a ton of time in the past.

Below are two SSMS Addins I used with good success.

ApexSQL Search – Searches both schema and data in databases and has additional features such as dependency tracking and more…

SSMS Tools pack – Has same search functionality as previous one and several other cool features. Not free for SQL Server 2012 but still very affordable.

I know this answer is not 100% related to the questions (which was more specific) but hopefully others will find this useful.

2013/09/05

I usually run the following to achieve that:

select distinct object_name(id) 
from syscomments 
where text like '%[ABD]%'
order by object_name(id) 
2013/02/05

Good practice to work with SQL Server.

Create below stored procedure and set short key,

CREATE PROCEDURE [dbo].[Searchinall]       
(@strFind AS VARCHAR(MAX))
AS
BEGIN
    SET NOCOUNT ON; 
    --TO FIND STRING IN ALL PROCEDURES        
    BEGIN
        SELECT OBJECT_NAME(OBJECT_ID) SP_Name
              ,OBJECT_DEFINITION(OBJECT_ID) SP_Definition
        FROM   sys.procedures
        WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'[email protected]+'%'
    END 

    --TO FIND STRING IN ALL VIEWS        
    BEGIN
        SELECT OBJECT_NAME(OBJECT_ID) View_Name
              ,OBJECT_DEFINITION(OBJECT_ID) View_Definition
        FROM   sys.views
        WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'[email protected]+'%'
    END 

    --TO FIND STRING IN ALL FUNCTION        
    BEGIN
        SELECT ROUTINE_NAME           Function_Name
              ,ROUTINE_DEFINITION     Function_definition
        FROM   INFORMATION_SCHEMA.ROUTINES
        WHERE  ROUTINE_DEFINITION LIKE '%'[email protected]+'%'
               AND ROUTINE_TYPE = 'FUNCTION'
        ORDER BY
               ROUTINE_NAME
    END

    --TO FIND STRING IN ALL TABLES OF DATABASE.    
    BEGIN
        SELECT t.name      AS Table_Name
              ,c.name      AS COLUMN_NAME
        FROM   sys.tables  AS t
               INNER JOIN sys.columns c
                    ON  t.OBJECT_ID = c.OBJECT_ID
        WHERE  c.name LIKE '%'[email protected]+'%'
        ORDER BY
               Table_Name
    END
END

Now - Set short key as below,

enter image description here

So next time whenever you want to find a particular text in any of the four objects like Store procedure, Views, Functions and Tables. You just need to write that keyword and press shortcut key.

For example: I want to search 'PaymentTable' then write 'PaymentTable' and make sure you select or highlight the written keyword in query editor and press shortcut key ctrl+4 - it will provide you full result.

2020/05/12

Redgate's SQL Search is a great tool for doing this, it's a free plugin for SSMS.

2014/09/12