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

User · Answer

I usually run the following to achieve that   select distinct object name id   from syscomments  where text like    ABD    order by object name id

User · 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

User · Answer

It might help you   SELECT DISTINCT        A NAME AS OBJECT NAME        A TYPE DESC       FROM SYS SQL MODULES M        INNER JOIN SYS OBJECTS A ON M OBJECT ID   A OBJECT ID       WHERE M DEFINITION LIKE       SEARCH TEXT            ORDER BY TYPE DESC

User · Answer

This query is search text in stored procedure from all databases   DECLARE  T Find Text VARCHAR 1000     Foo   IF OBJECT ID  tempdb   T DBNAME   IS NOT NULL DROP TABLE  T DBNAME IF OBJECT ID  tempdb   T PROCEDURE   IS NOT NULL DROP TABLE  T PROCEDURE  CREATE TABLE  T DBNAME       IDX int IDENTITY 1 1  PRIMARY KEY        DBName VARCHAR 255     CREATE TABLE  T PROCEDURE       IDX int IDENTITY 1 1  PRIMARY KEY        DBName VARCHAR 255        Procedure Name VARCHAR MAX        Procedure Description VARCHAR MAX     INSERT INTO  T DBNAME  DBName  SELECT name FROM master dbo sysdatabases  DECLARE  T C IDX INT   0 DECLARE  T C DBName VARCHAR 255  DECLARE  T SQL NVARCHAR MAX  DECLARE  T SQL PARAM NVARCHAR MAX    SET  T SQL PARAM               T C DBName VARCHAR 255             T Find Text VARCHAR 255            WHILE EXISTS SELECT TOP 1 IDX FROM  T DBNAME WHERE IDX  gt   T C IDX ORDER BY IDX ASC  BEGIN      SELECT TOP 1       T C DBName   DBName      FROM  T DBNAME WHERE IDX  gt   T C IDX ORDER BY IDX ASC      SET  T SQL           SET  T SQL    T SQL    INSERT INTO  T PROCEDURE DBName  Procedure Name  Procedure Description       SET  T SQL    T SQL    SELECT SPECIFIC CATALOG  ROUTINE NAME  ROUTINE DEFINITION       SET  T SQL    T SQL    FROM      T C DBName      INFORMATION SCHEMA ROUTINES        SET  T SQL    T SQL    WHERE ROUTINE DEFINITION LIKE         T Find Text               SET  T SQL    T SQL    AND ROUTINE TYPE     PROCEDURE          BEGIN TRY         EXEC SP EXECUTESQL   T SQL   T SQL PARAM   T C DBName   T Find Text     END TRY     BEGIN CATCH         SELECT  T C DBName     ERROR      END CATCH      SET  T C IDX    T C IDX   1 END  SELECT IDX  DBName  Procedure Name FROM  T PROCEDURE ORDER BY DBName ASC

User · Answer

SELECT DISTINCT OBJECT NAME  id    text     FROM syscomments      WHERE  id  IN  SELECT  id  FROM sysobjects WHERE xtype IN      TF   FN   V   P   AND status  gt   0  AND       text  LIKE   text to be search        OBJECT NAME  id   --  Object Name  View Store Procedure Scalar Function Table function name   id   int    Object identification number  xtype   char 2  Object type  Can be one of the following object types   FN   Scalar function  P   Stored procedure  V   View  TF   Table function

User · Answer

Try this request   Query  SELECT name FROM   sys procedures WHERE  Object definition object id  LIKE   strHell

User · Answer

Select distinct OBJECT NAME id  from syscomments where text like   string   AND OBJECTPROPERTY id   IsProcedure     1

User · Answer

Also you can use   SELECT OBJECT NAME id       FROM syscomments      WHERE  text  LIKE   flags         AND OBJECTPROPERTY id   IsProcedure     1      GROUP BY OBJECT NAME id    Thats include comments

User · Answer

I created a procedure to search text in procedures functions  tables  views  or jobs  The first parameter  search is the search criterion   target the search target  i e   procedures  tables  etc  If not specified  search all   db is to specify the database to search  default to your current database  Here is my query in dynamic SQL   ALTER PROCEDURE  dbo   usp find objects         search VARCHAR 255        target VARCHAR 255    NULL       db VARCHAR 35    NULL   AS  SET NOCOUNT ON   DECLARE  TSQL NVARCHAR MAX    USEDB NVARCHAR 50   IF  db  lt  gt     SET  USEDB    USE      db ELSE SET  USEDB       IF  target IS NULL SET  target       SET  TSQL    USEDB      DECLARE  search VARCHAR 128   DECLARE  target VARCHAR 128   SET  search           search        SET  target          target        IF  target LIKE    Procedure    BEGIN     SELECT o name As   Stored Procedures       FROM SYSOBJECTS o      INNER JOIN SYSCOMMENTS c ON o id   c id     WHERE c text LIKE  search         AND o xtype IN    P     FN        GROUP BY o name     ORDER BY o name END  ELSE IF  target LIKE    View    BEGIN     SELECT o name As   Views       FROM SYSOBJECTS o      INNER JOIN SYSCOMMENTS c ON o id   c id     WHERE c text LIKE  search         AND o xtype     V       GROUP BY o name     ORDER BY o name END     Table - search table name only  need to add column name    ELSE IF  target LIKE    Table    BEGIN     SELECT t name AS   TableName       FROM sys columns c      JOIN sys tables t ON c object id   t object id     WHERE c name LIKE  search     ORDER BY TableName END  ELSE IF  target LIKE    Job    BEGIN     SELECT  j job id          s srvname          j name          js step id          js command          j enabled      FROM     msdb  dbo sysjobs j     JOIN     msdb  dbo sysjobsteps js         ON  js job id   j job id      JOIN    master dbo sysservers s         ON  s srvid   j originating server id     WHERE   js command LIKE  search END  ELSE BEGIN      SELECT o name As   Stored Procedures       FROM SYSOBJECTS o      INNER JOIN SYSCOMMENTS c ON o id   c id     WHERE c text LIKE  search         AND o xtype IN    P     FN        GROUP BY o name     ORDER BY o name      SELECT o name As   Views       FROM SYSOBJECTS o      INNER JOIN SYSCOMMENTS c ON o id   c id     WHERE c text LIKE  search         AND o xtype     V       GROUP BY o name     ORDER BY o name      SELECT t name AS   Tables       FROM sys columns c      JOIN sys tables t ON c object id   t object id     WHERE c name LIKE  search     ORDER BY Tables      SELECT  j name AS   Jobs       FROM     msdb  dbo sysjobs j     JOIN     msdb  dbo sysjobsteps js         ON  js job id   j job id      JOIN    master dbo sysservers s         ON  s srvid   j originating server id     WHERE   js command LIKE  search END    EXECUTE sp executesql  TSQL      Update    If you renamed a procedure  it only updates sysobjects but not syscomments  which keeps the old name and therefore that procedure will not be included in the search result unless you drop and recreate the procedure

User · Answer

Every so often I use this script to figure out which procs to modify  or to figure out what uses a column of a table  or that table at all to remove some old junk   It checks each database on the instance it is ran on by the wonderfully supplied sp msforeachdb   if object id  tempdb    nothing   is not null     drop table   nothing  CREATE TABLE   nothing       DatabaseName varchar 30       SchemaName varchar 30       ObjectName varchar 100       ObjectType varchar 50     EXEC master sys sp msforeachdb   USE   insert into   nothing SELECT  db name   AS  Database    Scehma  schema name o schema id    o Name   o type  FROM sys sql modules m INNER JOIN sys objects o     ON o object id   m object id WHERE      m definition like    SOME TEXT       --edit this text  SELECT   FROM   nothing n order by OBJECTname

User · Answer

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

User · Answer

Please take this as a  dirty  alternative but this saved my behind many times especially when I was not familiar with the DB project  Sometimes you are trying to search for a string within all SPs and forget that some of the related logic may have been hiding between Functions and Triggers or it can be simply worded differently than you thought    From your MSSMS you may right click your DB and select Tasks - gt  Generate Scripts wizard to output all the SPs  Fns and Triggers into a single  sql file      Make sure to select Triggers too     Then just use Sublime or Notepad to search for the string you need to find  I know this may be quite inefficient and paranoid approach but it works

User · Answer

Using CHARINDEX   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 CHARINDEX   ABD   m definition   gt 0     Using PATINDEX   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 PATINDEX     ABD   m definition   gt 0      Using this double    ABD  is similar to escaping    WHERE m definition LIKE      ABD

User · Answer

select top 10   from sys procedures where object definition object id  like     ABD

User · Answer

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    String

User · Answer

also try this        SELECT ROUTINE NAME      FROM INFORMATION SCHEMA ROUTINES      WHERE ROUTINE DEFINITION like     ABD

User · Answer

You can also use  CREATE PROCEDURE  Search        Filter nvarchar max    AS BEGIN  SELECT name FROM   procedures WHERE   definition LIKE      Filter      END   and then run  exec  Search   text

User · Answer

-- Applicable for SQL 2005  USE YOUR DATABASE NAME         GO  SELECT  Scehma    schema name o schema id       o NAME      o type FROM sys sql modules m INNER JOIN sys objects o ON o object id   m object id WHERE m DEFINITION LIKE   YOUR SEARCH KEYWORDS   GO

User · Answer

You can also use this one   SELECT    FROM INFORMATION SCHEMA ROUTINES  WHERE ROUTINE DEFINITION like   Search String

User · Answer

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      strFind         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      strFind         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      strFind                    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      strFind             ORDER BY                Table Name     END END   Now - Set short key as below     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

User · Answer

SEARCH SPROCS  amp  VIEWS      The following query will allow search within the definitions      of stored procedures and views       It spits out the results as XML  with the full definitions       so you can browse them without having to script them individually             STEP 1  POPULATE SEARCH KEYS   Set to NULL to ignore     DECLARE       def key varchar 128      foo             lt  lt  lt  definition search key         name key varchar 128      bar            lt  lt  lt  name search key               schema key varchar 128     dbo           lt  lt  lt  schema search key          WITH SearchResults AS                  STEP 2  DEFINE SEARCH QUERY AS CTE  Common Table Expression             SELECT           Object  object id                       AS  object id                Schema  name                            AS  schema name             Object  name                            AS  object name            Object  type                            AS  object type            Object  type desc                       AS  object type desc            Details  definition                     AS  module definition      FROM              sys sql modules   where the body of sprocs and views live            sys sql modules AS  Details  WITH  NOLOCK      JOIN            sys objects   where the metadata for every object in the database lives            sys objects AS  Object  WITH  NOLOCK  ON  Details  object id    Object  object id     JOIN             sys schemas   where the schemas in the datatabase live            sys schemas AS  Schema  WITH  NOLOCK  ON  Object  schema id    Schema  schema id     WHERE            def key IS NULL OR  Details  definition LIKE  def key           lt  lt  lt  searches definition            AND   name key IS NULL OR  Object  name LIKE  name key            lt  lt  lt  searches name                  AND   schema key IS NULL OR  Schema  name LIKE  schema key        lt  lt  lt  searches schema                 STEP 3  SELECT FROM CTE INTO XML             This outer select wraps the inner queries in to the  lt sql object gt  root element     SELECT                    This inner query maps stored procedure rows to  lt procedure gt  elements            SELECT TOP 100 PERCENT          object id                             AS   object id             schema name           object name     AS   full name            module definition                     AS  module definition      FROM         SearchResults     WHERE         object type    P      ORDER BY          schema name    object name      FOR XML         PATH   procedure    TYPE   AS  procedures        lt  lt  lt  as part of the outer query                              this alias causes the  lt procedure gt  elements                            to be wrapped within the  lt procedures gt  element                      This inner query maps view rows to  lt view gt  elements            SELECT TOP 100 PERCENT           object id                             AS   object id             schema name           object name     AS   full name            module definition                     AS  module definition      FROM         SearchResults     WHERE         object type    V      ORDER BY          schema name    object name      FOR XML         PATH   view    TYPE   AS  views       lt  lt  lt  as part of the outer query                        this alias causes the  lt view gt  elements                      to be wrapped within the  lt views gt  element    FOR XML      PATH   sql objects

User · Answer

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

[sql] Search text in stored procedure in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to stored-procedures