SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Search all code for specific keyword
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 03/14/2008 :  09:09:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This will work on SQL Server 2005 and later.
Since the code is building an XML string, keywords overlapping the magic 4000 character limit are fetched!
SELECT	p.RoutineName, 
	'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec] 
FROM	( 
		SELECT	OBJECT_NAME(so.ID) AS RoutineName, 
			(SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR XML PATH('')) AS Body 
		FROM	SYSOBJECTS AS so 
		WHERE	so.TYPE IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'V', 'X') 
	) AS p 
WHERE	p.Body LIKE '%YourKeyWordHere%'

E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 03/14/2008 11:54:50

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/14/2008 :  09:33:50  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
I like the output -- very nice idea!

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 03/14/2008 :  11:54:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The types are
C = CHECK constraint 
D = Default or DEFAULT constraint 
FN = Scalar function 
IF = In-lined table-function 
P = Stored procedure 
R = Rule 
RF = Replication filter stored procedure 
TF = Table function 
TR = Trigger 
V = View 
X = Extended stored procedure

I were suggested to use sys.sql_modules instead but then types C and X are not shown.


E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 03/14/2008 11:55:51
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 03/14/2008 :  15:23:36  Show Profile  Reply with Quote
I really like this script.

As a suggestion, you may want to include the schema in the object name, in case the object is not dbo.


select
	--p.RoutineName, 
	'exec sp_helptext ''' +p.RoutineName+'''' AS [Exec] 
from	( 
	select
		quotename(user_name( so.uid ))+'.'+
		quotename(object_name(so.id)) as RoutineName, 
		(
		select top 100 percent
			'' + sc.text
		from
			syscomments as sc
		where
			sc.id = so.id
		order by
			sc.colid
		for xml path('')
		) as body 
	from
		sysobjects as so
	where
		so.type in
		('C','D','FN','IF','P','R','RF','TF','TR','V','X')
	) as p 
where
	-- String to search for
	p.Body LIKE '%##%'


Results:
Exec 
-------------------------------------------------------
exec sp_helptext '[DevGuy].[MyBadlyCodedProc]'
exec sp_helptext '[dbo].[MyBadlyCodedFunction]'



CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 03/14/2008 :  18:22:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Excellent. Thank you both.
Throw in an ORDER BY p.RoutineName also and I am happy.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22773 Posts

Posted - 03/15/2008 :  04:48:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
In previous versions, script out to text file and do search
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/13/script-out-procedures-and-functions-part-2.aspx


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000