Technical Article

Find Text in Database Objects (triggers / stored procedures)


I find myself refacoring an existing database which has some inefficient code, like cursors in stored procedures and triggers ...

Wanting to figure out which objects contained this "poor" code, I made myself a little script to list all the objects which contain a search string.

Just change the value that @search is set to and run the script in the database you wish to search.


Object type. Can be one of these object types: 
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure 
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
set nocount on

declare @search varchar(50)
set @search = 'cursor'

declare @t_obj table(rowid int identity, obj nvarchar(128), xtype varchar(2))
create table #t_scratch (rowid int identity, t nvarchar(256))
declare @t_obj_text table(rowid int identity, obj nvarchar(128), t nvarchar(256))
declare @rowid int, @obj nvarchar(128)

insert into @t_obj (obj, xtype)
select name, xtype from sysobjects where xtype in ('p', 'fn', 'tf', 'tr')
select @rowid = @@rowcount

while @rowid > 0
select @obj = obj from @t_obj where rowid = @rowid
delete #t_scratch
insert into #t_scratch
exec sp_helptext @obj
insert into @t_obj_text (obj, t)
select @obj, t from #t_scratch order by rowid
select @rowid = @rowid - 1

drop table #t_scratch

select ot.obj, o.xtype, count(*) [search]
from @t_obj_text [ot]
inner join @t_obj [o]
on ot.obj = o.obj
where t like '%'+@search+'%'
group by ot.obj, o.xtype
order by o.xtype, ot.obj


5 (1)




5 (1)