SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Search for string occurrence


Search for string occurrence

Author
Message
Rog Saber
Rog Saber
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 231

Can this be done?

Does anyone know of a tool or command I could use that can find all occurrences of a particular string?

I want to look in all of my procs and triggers.

For example, I have an event_code column on some tables. I reference that column in my procs and triggers. In my scripts, I may have something like @event_code = 'DDENT'. I want to search through all procs and triggers for 'DDENT'.

I have all of my procs & triggers in .sql files and I could search using windows explorer for the DDENT string but I don't trust the results.

Thank you


Daryl AZ
Daryl AZ
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 698
You can script all database objects into one file. Then simply search through the file for all objects. Works with the MMC plugin for 7/2000.
Rog Saber
Rog Saber
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 231

"You can script all database objects into one file." How do I do this?

What is MMC plugin for 7/2000 and how/where can I get it?


SQLZ
SQLZ
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3158 Visits: 940

select distinct object_name(id) from syscomments where text like '%DDENT%'

This will return the object names that contain the string "DDENT".

Hope that helps,



Karl
source control for SQL Server
Rog Saber
Rog Saber
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 231

Karl -

That worked great - thanks!


Daryl AZ
Daryl AZ
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 698
The MMC is the enterprise consule. Basically right click on the database and script to file. It should let you select all objects.

The 2nd option appears to work better but I was not familiar with it.

good luck
daralick
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68817 Visits: 9671
SysComments is a system table that holds all the scripts of all the objects of the database (sps, triggers, views...). So this technic is really fast an effective to find out stuff like that.
vadba
vadba
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2818 Visits: 487
The [text] column in syscomments is nvarchar(4000). If the length of the text for the object is greater that 4000, it will be split into two or more rows. Be advised that when searching syscomments with LIKE, there is a slight possibility that the string you are searching for could be split and cross over into the next row. In that case, LIKE will not find a match. Granted, it's a relatively remote possibility, but it could happen.



Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68817 Visits: 9671
Ya I had to code for that once... Since I had a application I did 2 searchs... The first search was only considering objects with one entry, then the second search downloaded the code in the application, the app concatenated all parts into a single script and then the search was ran. I'm sure a similar process could be done in a dts but I don't have the time to code it now.
SQLZ
SQLZ
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3158 Visits: 940

Good point.

You can do this entirely in T-SQL by concatenating the [text] field into a seperate table that has a field with a text datatype (thus allowing more than 8000 characters). You can then safely perform a like comparison.

It's a bit awkward though and given how remote the chances of this occurring are, unless it's an essential part of an app you're building, you might as well just script the procs and do a Find/Replace search. Or accept the risk.



Karl
source control for SQL Server
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search