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

CodeObjectsContaining Function - SQL Server

RSS
Modified on Tue, Nov 17, 2009, 6:00 PM by Administrator Categorized as SQL Server

SQL Server 2000

{copytext|Ss2000}
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

{copytext|Ss2005}
create function dbo.CodeObjectsContaining(
    @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-2018, Patrick Jasinski.