Posted by Erland Sommarskog on 07/23/06 18:13

(gjvdkamp@gmail.com) writes:
> CREATE proc [FindInObjects] (@Search varchar(300))
> as
>
> SELECT so.xtype, so.name, sc.TEXT
> FROM dbo.syscomments sc RIGHT OUTER JOIN
> dbo.sysobjects so ON sc.id = so.id
> WHERE so.xtype IN ('P', 'V', 'FN', 'TR') AND (sc.TEXT LIKE '%' +
> @Search + '%')
>
> This will show you a list of all the objects that contain the search
> string.

....unless the procedure name in question has been split up over a
chunk border. Keep in mind that the procedure text in syscomments is
split up in chunks of 4000 characters, and the split can well be in
the middle of an identifier.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Cryptography API for MS SQL Server
• Prev in forum: Re: Check if a Table is used in any sp or function
• Thread view: Re: Check if a Table is used in any sp or function

[Reply to this message]