[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

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
0
anushahanna
Asked:
anushahanna
  • 3
  • 3
  • 3
2 Solutions
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now