Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Page History: CodeObjectsContaining Function - SQL Server

Compare Page Revisions



« 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2022, Patrick Jasinski.