Technical Article

SP to search String in TEXT of SPs

,

Well this is an addendum to my earlier post for searching a specific string in all the Stored Procedures defined in a database and returning the name of the stored procedure in which it is contained. This is optimized based on suggestion of Greg and I thank him for his advise.

CREATE PROCEDURE SEARCHFORSTRING @SEARCHSTRING VARCHAR(100)
AS
			SELECT 	DISTINCT(sysobjects.name)
				
		        FROM 
				sysobjects,syscomments

			WHERE 
				sysobjects.id =     syscomments.id

			AND 
				sysobjects.type = 'P'
			AND 
				sysobjects.category=0
AND
		CHARINDEX(@SEARCHSTRING ,syscomments.text)>0

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (1)

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (1)

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

A Normalization Primer

For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

2003-01-13

18,069 reads