?
Solved

best system table to search code

Posted on 2011-03-08
9
Medium Priority
?
266 Views
Last Modified: 2012-05-11
what is the best system table in 2000 and 2005+ to search for specific text in a code (UDF or proc) etc

thanks
0
Comment
Question by:anushahanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 1000 total points
ID: 35073404

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35073616
OK-

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

what about in 2005 & 2008?
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1000 total points
ID: 35073665
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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 6

Author Comment

by:anushahanna
ID: 35073716
good point - INFORMATION_SCHEMA.ROUTINES also will work on all versions, right?
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35073729
correct, INFORMATION_SCHEMA.ROUTINES for all versions
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35073845
"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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35074066

@icohan
What are you talking about?

This question is about UDFs and Stored Procedures
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35074170
Right - my bad...overlooked that and thought is ALL sql code objects.
Please disregard my answer.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35077209
thanks to both of you!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question