How do I find a stored procedure containing text

Question

I need to search a SQL server 2008 for stored procedures containing where  maybe  the name of a database field or variable name

User · Answer

How to Find a Stored Procedure Containing Text or String

Many time we need to find the text or string in the stored procedure. Here is the query to find the containing text.

SELECT OBJECT_NAME(id) 
FROM SYSCOMMENTS 
WHERE [text] LIKE '%Text%' 
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
GROUP BY OBJECT_NAME(id)

For more information please check the given URL given below.

http://www.freshcodehub.com/Article/34/how-to-find-a-stored-procedure-containing-text-or-string

User · Answer

SELECT ROUTINE NAME  ROUTINE DEFINITION  FROM INFORMATION SCHEMA ROUTINES  WHERE ROUTINE DEFINITION LIKE   FieldName    AND ROUTINE TYPE  PROCEDURE

User · Answer

SELECT name FROM sys procedures WHERE Object definition object id  LIKE   FOO

User · Answer

SELECT   FROM sys procedures WHERE Name LIKE   CompanySpecialisation     This is what I have written in SQL 2012 to select all stored procedures where name like  CompanySpecialisation

User · Answer

SELECT ROUTINE NAME  ROUTINE DEFINITION     FROM INFORMATION SCHEMA ROUTINES      WHERE ROUTINE DEFINITION LIKE   Foo        AND ROUTINE TYPE  PROCEDURE      SELECT OBJECT NAME id       FROM SYSCOMMENTS      WHERE  text  LIKE   Foo        AND OBJECTPROPERTY id   IsProcedure     1      GROUP BY OBJECT NAME id      SELECT OBJECT NAME object id      FROM sys sql modules     WHERE OBJECTPROPERTY object id   IsProcedure     1     AND definition LIKE   Foo

User · Answer

Here is the union version that use Losbear made  but modified so that it also outputs the schema name   DECLARE  SearchText varchar 1000     searchtext    SELECT DISTINCT sc SPECIFIC SCHEMA as  Schema   sp SPName as  Name  FROM        SELECT ROUTINE NAME SPName         FROM INFORMATION SCHEMA ROUTINES          WHERE ROUTINE DEFINITION LIKE        SearchText                AND ROUTINE TYPE  PROCEDURE       UNION ALL      SELECT OBJECT NAME id  SPName         FROM SYSCOMMENTS          WHERE  text  LIKE        SearchText                AND OBJECTPROPERTY id   IsProcedure     1          GROUP BY OBJECT NAME id       UNION ALL      SELECT OBJECT NAME object id  SPName         FROM sys sql modules         WHERE OBJECTPROPERTY object id   IsProcedure     1         AND definition LIKE        SearchText          AS sp CROSS APPLY  select SPECIFIC SCHEMA from INFORMATION SCHEMA ROUTINES r where r ROUTINE NAME   sp SPName  sc ORDER BY 1

User · Answer

You can also try ApexSQL Search - free SSMS plug-in from ApexSQL

User · Answer

For any SQL server newer than SQL server 2000   SELECT object name   OBJECT NAME sm object id   o type desc  sm definition   FROM sys sql modules AS sm   JOIN sys objects AS o ON sm object id   o object id   WHERE sm definition like   searchString     ORDER BY  o type  o name  o object id   If someone is stuck with SQL server 2000  the table sql modules doesn t exist  so you would use syscomments  you will get multiple records for stored procdedures larger than 4000 characters  but they will have the same c number field so you can group the parts together to get the full stored procedure text         Select o id  c number  o name  c text       from syscomments c      inner join sysobjects o on o id   c id      where c encrypted   0 and o type    P          and c id in         Select id from syscomments where text like   searchtext        order by objecttype  o name  o id  c number  c colid

User · Answer

Stored Procedure for find text in SP    Dinesh Baskaran  Trendy Global Systems pvt ltd      create Procedure  dbo   TextFinder     Text varchar 500   Type varchar 2  NULL  AS BEGIN      SELECT DISTINCT o name AS ObjectName   CASE o xtype  WHEN  C  THEN  CHECK constraint    WHEN  D  THEN  Default or DEFAULT constraint  WHEN  F  THEN  FOREIGN KEY constraint  WHEN  FN  THEN  Scalar function  WHEN  IF  THEN  In-lined table-function  WHEN  K  THEN  PRIMARY KEY or UNIQUE constraint  WHEN  L  THEN  Log  WHEN  P  THEN  Stored procedure  WHEN  R  THEN  Rule  WHEN  RF  THEN  Replication filter stored procedure   WHEN  S  THEN  System table    WHEN  TF  THEN  Table function   WHEN  TR  THEN  Trigger    WHEN  U  THEN  User table   WHEN  V  THEN  View   WHEN  X  THEN  Extended stored procedure   ELSE o xtype  END AS ObjectType     ISNULL  p Name    db    AS Location  FROM syscomments c  INNER JOIN sysobjects o ON c id o id  LEFT JOIN sysobjects p ON o Parent obj p id  WHERE c text LIKE        Text       and  o xtype   case when  Type IS NULL then o xtype  else  Type end    ORDER BY Location  ObjectName    END

User · Answer

sp msforeachdb  use   select name       from sys procedures where object definition object id  like    text       This will search in all stored procedure of all databases  This will also work for long procedures

User · Answer

SELECT OBJECT NAME id       FROM syscomments  WHERE  text  LIKE   Name    AND OBJECTPROPERTY id   IsProcedure     1  GROUP BY OBJECT NAME id    Try This

User · Answer

Grab yourself a copy of the free Red-Gate SQL Search tool and start enjoying searching in SQL Server   -     It s a great and very useful tool  and YES  it s totally  absolutely FREE for any kind of use

User · Answer

select   from sys system objects where name like   cdc

User · Answer

First ensure that you re running the query under your user credentials  and also in the right database context   USE YOUR DATABASE NAME    Otherwise  sys procedures won t return anything  Now run the query as below   select   from sys procedures p  join sys syscomments s on p object id   s id  where text like   YOUR TEXT      Another option is to use INFORMATION SCHEMA ROUTINES ROUTINE DEFINITION  but be aware that it only holds limited number of characters  i e   first 4000 characters  of the routine   select   from YOUR DATABASE NAME INFORMATION SCHEMA ROUTINES where ROUTINE DEFINITION like   YOUR TEXT      I tested on Microsoft SQL Server 2008 R2  SP1  - 10 50 2500 0  X64

User · Answer

I tried above example but it was not showing more than 4000 characters then I modified it little bit and was able to get whole stored procedure definition  Please see the updated script below -  SELECT SCHEMA NAME O SCHEMA ID   SCHEMA NAME   O NAME  OBJECT DEFINITION OBJECT ID  TEXT FROM   SYSCOMMENTS AS C        INNER JOIN SYS OBJECTS AS O ON C ID   O  OBJECT ID         INNER JOIN SYS SCHEMAS AS S ON O SCHEMA ID   S SCHEMA ID WHERE  OBJECT DEFINITION OBJECT ID  LIKE   FOO   ORDER BY  SCHEMA NAME O SCHEMA ID   O NAME

User · Answer

In case you needed schema as well   SELECT   DISTINCT SCHEMA NAME o schema id  o name  text  FROM     syscomments AS c          INNER JOIN sys objects AS o ON c id   o  object id           INNER JOIN sys schemas AS s ON o schema id   s schema id WHERE    text LIKE   foo   ORDER BY  SCHEMA NAME o schema id  o name

User · Answer

create Procedure  dbo   TextFinder    Text varchar 500   Type varchar 2  NULL   AS BEGIN    SELECT DISTINCT o name AS ObjectName  CASE o xtype  WHEN  C  THEN  CHECK constraint   WHEN  D  THEN  Default or DEFAULT constraint   WHEN  F  THEN  FOREIGN KEY constraint   WHEN  FN  THEN  Scalar function   WHEN  IF  THEN  In-lined table-function   WHEN  K  THEN  PRIMARY KEY or UNIQUE constraint   WHEN  L  THEN  Log   WHEN  P  THEN  Stored procedure   WHEN  R  THEN  Rule   WHEN  RF  THEN  Replication filter stored procedure   WHEN  S  THEN  System table   WHEN  TF  THEN  Table function   WHEN  TR  THEN  Trigger  enter code here   WHEN  U  THEN  User table   WHEN  V  THEN  View   WHEN  X  THEN  Extended stored procedure   ELSE o xtype  END AS ObjectType   ISNULL  p Name    db    AS Location  FROM syscomments c  INNER JOIN sysobjects o ON c id o id  LEFT JOIN sysobjects p ON o Parent obj p id  WHERE c text LIKE        Text       and  o xtype   case when  Type IS NULL then o xtype  else  Type end  ORDER BY Location  ObjectName  END

User · Answer

SELECT s name         o name ProcedureName   c text ProcedureSteps FROM   sys syscomments c  INNER JOIN sys objects o  ON  c id   o object id INNER JOIN sys schemas s  ON  o schema id   s schema id WHERE  o type    P  AND c text LIKE N  XXXX   ORDER BY s name         o name   c colid   This query returns the name and the content of any stored procedure where  XXXX  is is referenced within the stored procedure   This is quit usefull when finding procedures that reference a specific table view procedure

User · Answer

I took Kashif s answer and union d all of them together  Strangely  sometimes  I found results in one of the selects but not the other  So to be safe  I run all 3 when I m looking for something  Hope this helps   DECLARE  SearchText varchar 1000     mytext    SELECT DISTINCT SPName  FROM        SELECT ROUTINE NAME SPName         FROM INFORMATION SCHEMA ROUTINES          WHERE ROUTINE DEFINITION LIKE        SearchText                AND ROUTINE TYPE  PROCEDURE       UNION ALL      SELECT OBJECT NAME id  SPName         FROM SYSCOMMENTS          WHERE  text  LIKE        SearchText                AND OBJECTPROPERTY id   IsProcedure     1          GROUP BY OBJECT NAME id       UNION ALL      SELECT OBJECT NAME object id  SPName         FROM sys sql modules         WHERE OBJECTPROPERTY object id   IsProcedure     1         AND definition LIKE        SearchText          AS T ORDER BY T SPName

User · Answer

I use this script   If you change your XML Comments to display as black text on a yellow background you get the effect of highlighting the text you re looking for in the xml column of the results    Tools -  Options -  Environment -  Fonts and Colors  Display items  XML Comment       ---------------------------------------------     --------------   Start  FINDTEXT   ----------     ---------------------------------------------      SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED      SET NOCOUNT ON     GO     DECLARE  SearchString VARCHAR MAX       SET  SearchString    the text you  re looking for      DECLARE  OverrideSearchStringWith VARCHAR MAX       --                                                                                  -- Use Escape chars in Brackets     like     to find percent char      --                                                                                    DECLARE  ReturnLen INT      SET  ReturnLen   50      with    lastrun               as  select    DEPS OBJECT ID                             MAX last execution time  as LastRun                   from      sys dm exec procedure stats DEPS                   group by  deps object id                            SELECT  OL Type                 OBJECT NAME OL Obj ID  AS  Name                  LTRIM RTRIM REPLACE SUBSTRING REPLACE OBJECT DEFINITION OL Obj ID   NCHAR 0x001F        CHARINDEX  SearchString  OBJECT DEFINITION OL Obj ID   -  ReturnLen   ReturnLen   2    SearchString         -- gt  gt      SearchString     lt  lt --          AS SourceLine                 CAST REPLACE REPLACE REPLACE REPLACE CONVERT VARCHAR MAX   REPLACE OBJECT DEFINITION OL Obj ID   NCHAR 0x001F           amp      A M P       lt      L T       gt      G T      SearchString    lt  -- gt      SearchString     lt -- gt    AS XML  AS  Hilight Search                   SELECT  processing-instruction A     REPLACE OBJECT DEFINITION OL Obj ID   NCHAR 0x001F                       FOR                  XML PATH                          TYPE                   AS  code                  Modded AS Modified                 LastRun as LastRun         FROM     SELECT CASE P type                           WHEN  P  THEN  Proc                            WHEN  V  THEN  View                            WHEN  TR  THEN  Trig                            ELSE  Func                          END AS  Type                          P OBJECT ID AS OBJ id                         P modify Date AS modded                         LastRun LastRun                  FROM   sys Objects P WITH  NOLOCK                          LEFT join lastrun on P object id   lastrun object id                  WHERE  OBJECT DEFINITION p OBJECT ID  LIKE        SearchString                               AND type IN   P    V    TR    FN    IF    TF                        --   AND lastrun LastRun  IS NOT null                   OL     OPTION   FAST 10       ---------------------------------------------     ----------------    END     -----------------     ---------------------------------------------     ---------------------------------------------

[sql-server] How do I find a stored procedure containing <text>?

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to stored-procedures