SearchInCode

Need to search for a string contained in a proc, view, function, or trigger?  You can use sys.syscomments and JOIN to sysobjects, but it is nice to do all the JOINing beforehand and simply use a nice object dedicated to searching.

USE Admin

GO

IF Object_ID('dbo.SearchInCode') Is Not Null

      DROP PROC dbo.SearchInCode

go

CREATE PROC dbo.SearchInCode

                        @DB varchar(50),

                        @Sort bit = 1,

                        @SearchText1 varchar(200),

                        @SearchText2 varchar(200) = Null,

                        @SearchText3 varchar(200) = Null,

                        @ShowSql bit = 0

AS

/*    DATE        AUTHOR            REMARKS

      1/8/13           PPaiva            Initial creation.

     

     

      DESCRIPTION

            For a given database, lists any procs, functions,

                  triggers or views that contain @SearchText1,

                  AND optionally @SearchText2,

                  AND optionally @SearchText3. 

      USAGE

            Exec Admin..SearchInCode 'Admin', 1, 'File'

            Exec Admin..SearchInCode 'Sopa', 1, 'EmployeeID'

            Exec Admin..SearchInCode 'Sopa', 1, 'EmployeeID', 'log'

            Exec Admin..SearchInCode 'Sopa', 1, 'EmployeeID', 'log', 'Activity', 1

*/

SET NOCOUNT ON

IF Object_ID('tempdb.dbo.##SearchInCode') Is Not Null

      DROP TABLE ##SearchInCode

CREATE TABLE ##SearchInCode(

      ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,

      Server varchar(100),

      DB varchar(200),

      ObjType varchar(50),

      SchemaName sysname,

      ObjName sysname,

      Text varchar(max),

      FullName varchar(300),

      SearchForTextRunDate datetime NOT NULL DEFAULT GetDate()

      )

DECLARE @Sql varchar(2000)

SET @Sql =

'SELECT ''' + @@ServerName + ''' Server,

            ''' + @DB + ''' DB,

            ot.Description ObjType,

            s.Name SchemaName,

            o.Name ObjName,

            c.Text,

            ''[' + @DB + '].['' +  s.name + ''].['' + o.name + '']'' FullName

FROM [' + @DB + '].dbo.syscomments c

JOIN [' + @DB + '].sys.objects o

      ON c.ID = o.object_id

LEFT JOIN Admin.dbo.infraObjectType ot

      ON ot.Type = o.Type COLLATE SQL_Latin1_General_CP1_CI_AS

JOIN [' + @DB + '].sys.schemas s

      ON s.schema_id = o.schema_id

WHERE 1 = 1'

IF @SearchText2 Is Null AND @SearchText3 Is Null

      SET @Sql = @Sql + '

  AND c.Text Like ''%' + @SearchText1 + '%'''

ELSE IF @SearchText3 Is Null

      SET @Sql = @Sql + '

  AND c.Text Like ''%' + @SearchText1 + '%''

  AND c.Text Like ''%' + @SearchText2 + '%'''

ELSE

      SET @Sql = @Sql + '

  AND c.Text Like ''%' + @SearchText1 + '%''

  AND c.Text Like ''%' + @SearchText2 + '%''

  AND c.Text Like ''%' + @SearchText3 + '%'''

IF @Sort = 1

      SET @Sql = @Sql + '

ORDER BY ot.Description, Substring(o.name, 1, 1)'

IF @ShowSql = 1

      Print @Sql

INSERT INTO ##SearchInCode (Server, DB, SchemaName, ObjType, ObjName, Text, FullName)

      Exec(@Sql)

Print '

SELECT *

FROM ##SearchInCode

ORDER BY 1

'

SELECT *

FROM ##SearchInCode

ORDER BY 1