best system table to search code

what is the best system table in 2000 and 2005+ to search for specific text in a code (UDF or proc) etc

thanks
LVL 6
anushahannaAsked:
Who is Participating?
 
Ephraim WangoyaCommented:

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
0
 
anushahannaAuthor Commented:
OK-

I guess in 2000, that is the only option, right?

what about in 2005 & 2008?
0
 
lcohanDatabase AnalystCommented:
Always and all versions I use:

usemydb
go
select * from sys.syscomments where text like '%something%'
go

this will return ALL unencrypted SQL code objects containing "something"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
anushahannaAuthor Commented:
good point - INFORMATION_SCHEMA.ROUTINES also will work on all versions, right?
0
 
Ephraim WangoyaCommented:
correct, INFORMATION_SCHEMA.ROUTINES for all versions
0
 
lcohanDatabase AnalystCommented:
"Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL."

http://msdn.microsoft.com/en-us/library/ms188757%28SQL.90%29.aspx

Where do you get the Views and Triggers code from?
As far as I'm aware they are ALL in sys.syscomments.text column

0
 
Ephraim WangoyaCommented:

@icohan
What are you talking about?

This question is about UDFs and Stored Procedures
0
 
lcohanDatabase AnalystCommented:
Right - my bad...overlooked that and thought is ALL sql code objects.
Please disregard my answer.
0
 
anushahannaAuthor Commented:
thanks to both of you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.