About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Tuesday, January 8, 2013

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




1 comment:

  1. My partner and i actually enjoy this post and the internet site all in all! Your piece of writing is really plainly composed as well as simply understandable. Your current Blog design is awesome as well! Would be awesome to know where I are able obtain it. Please maintain up the very good job. We all require far more such website owners like you on the net and much fewer spammers. Fantastic mate!
    Hadoop online training

    ReplyDelete