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
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!
ReplyDeleteHadoop online training