Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Search for string occurrence Expand / Collapse
Author
Message
Posted Wednesday, August 30, 2006 9:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 12:39 PM
Points: 146, Visits: 198

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

Post #305030
Posted Wednesday, August 30, 2006 9:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:23 PM
Points: 199, Visits: 590
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.

Post #305034
Posted Wednesday, August 30, 2006 9:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 12:39 PM
Points: 146, Visits: 198

"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?

Post #305039
Posted Wednesday, August 30, 2006 9:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:52 AM
Points: 870, Visits: 884

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
Post #305040
Posted Wednesday, August 30, 2006 9:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 12:39 PM
Points: 146, Visits: 198

Karl -

That worked great - thanks!

Post #305041
Posted Wednesday, August 30, 2006 10:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:23 PM
Points: 199, Visits: 590
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
Post #305092
Posted Thursday, August 31, 2006 8:12 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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.
Post #305353
Posted Thursday, August 31, 2006 8:44 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:12 AM
Points: 977, Visits: 268
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.



Post #305374
Posted Thursday, August 31, 2006 8:51 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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.
Post #305379
Posted Thursday, August 31, 2006 8:58 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:52 AM
Points: 870, Visits: 884

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
Post #305385
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse