Page History: CodeObjectsContaining Function - SQL Server

Compare Page Revisions

0 Current 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Wed, Aug 19, 2009, 1:48 PM

SQL Server 2000¶

declare @TextSought varchar(100)

set @TextSought = 'ErrorMessage'

select distinct top 100 percent 	
	obj_type 	= o.type, 
	obj_name 	= o.name,
	parent_type = p.type,
	parent_name = p.name

from 
	sysobjects o
	inner join syscomments c
		on o.id = c.id
	left join sysobjects p
		on o.parent_obj = p.id

where 1=1
	and c.text like '%' + @TextSought + '%'
	and o.name <> @TextSought

order by 
	o.type, 
	o.name

SQL Server 2005¶

create function dbo.udtCodeObjectsContaining(
    @TextSought varchar(100)
    ) returns table as return

--declare @TextSought varchar(100)
--set @TextSought = 'employee'


select distinct top 100 percent
     ObjType = o.type
    ,ObjDesc = o.type_desc
    ,ObjName = o.name
    ,ParentType = p.type
    ,ParentDesc = p.type_desc
    ,ParentName = p.name

from 
    sys.objects o

    inner join sys.sql_modules m
        on o.object_id = m.object_id

    left join sys.objects p
        on o.parent_object_id = p.object_id

where 1=1
    and m.definition like '%' + @TextSought + '%'
    and o.name <> @TextSought

order by
     o.type_desc
    ,o.name