SELECT SQL and upgrade your Performance - Search text in Triggers and Store Prodedures

Search text in Triggers and Store Prodedures

Written by Stanislav Duben

Friday, 24 August 2007

If you want search text not only in name of triggers and store procedures, using search object in query analyzer is not enough and in MS SQL 2005 there isn't any search object function. So you need other way how to do it.

There is code for searching in xtype objects, that contains body of triggers, store procedures and other objects inside MS SQL database. This example show you how to search in triggers and store procedures.

DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = 'Text I am searching', @notcontain = ''

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
AND ((CHARINDEX(@notcontain,syscomments.text)=0 
or CHARINDEX(@notcontain,syscomments.text)<>0))

Into @SEARCHSTRING you can assign any text you need search. If you want exclude some results with specify text, you can use @notcontain variable.

You can use also other objects for searching. Then you need change case condition, and add other xtypes you want to use. Then change where condition, add sysobjects.type in association to used xtypes. List of possible objects of sysobjects.type and sysobjects.xtype is bellow and is very similar. There is only one different, type K (primary key or UNIQUE constraint) is in xtype splitted to PK (primary key) and UQ (UNIQUE contraint).

List of sysobjects.type

C = CHECK constraint 
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint 
K = PRIMARY KEY or UNIQUE constraint 
L = Log
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
S = System table 
TR = Trigger
U = User table
V = View
X = Extended stored procedure

List of sysobjects.xtypes

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure 
S = System table
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

Tag it: