Solved

best system table to search code

Posted on 2011-03-08
9
261 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
  • 3
  • 3
  • 3
9 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 250 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 39

Assisted Solution

by:lcohan
lcohan earned 250 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
 
LVL 6

Author Comment

by:anushahanna
ID: 35073716
good point - INFORMATION_SCHEMA.ROUTINES also will work on all versions, right?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 32

Expert Comment

by:ewangoya
ID: 35073729
correct, INFORMATION_SCHEMA.ROUTINES for all versions
0
 
LVL 39

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:ewangoya
ID: 35074066

@icohan
What are you talking about?

This question is about UDFs and Stored Procedures
0
 
LVL 39

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now