<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Joshua A. Walker
»
Search All SQL Script Objects in All...
Search All SQL Script Objects in All Databases for Text
Rate Topic
Display Mode
Topic Options
Author
Message
jwalker8680
jwalker8680
Posted Friday, February 06, 2009 1:58 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 26, 2013 12:28 PM
Points: 30,
Visits: 110
Comments posted to this topic are about the item
Search All SQL Script Objects in All Databases for Text
Post #651977
JacekO
JacekO
Posted Thursday, February 19, 2009 7:49 AM
SSC-Addicted
Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473,
Visits: 606
There is one problem with your script: it will not find all instances of the text in SPs, Views or functions that are larger then 4000 characters and the searchabe text is close to the 4000 character position. Because SQL Server will split the code between multiple syscomments records you may end up with 'Joshua' in one syscomments record and 'A Walker' in another.
In order to prevent this you could use something similar
SELECT DISTINCT name
FROM syscomments SC
INNER JOIN sysobjects SO ON SC.id = SO.id
WHERE SC.text LIKE '%' + @FieldName + '%'
UNION
SELECT DISTINCT name
FROM syscomments AS L1
INNER JOIN sysobjects SO ON L1.id = SO.id
INNER JOIN syscomments AS L2 ON L1.id = L2.id
AND L1.colid = L2.colid - 1
WHERE RIGHT(L1.text, 50) + LEFT(L2.text, 50) LIKE '%' + @FieldName + '%'
This is what I use when looking for a text in the SQL code. It only works for one database but so far I did not have a need to search all databases on a server.
---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #660330
shelleybobelly
shelleybobelly
Posted Friday, March 13, 2009 10:23 AM
Grasshopper
Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 16,
Visits: 508
Don't forget to clean up and drop the #Results table if running as a script not a procedure.
Post #675385
Yenidai
Yenidai
Posted Monday, March 16, 2009 12:58 AM
Old Hand
Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 334,
Visits: 83
This script crash when a database is offline:
A simple where condition should is missing:
SELECT [NAME] FROM Master.dbo.sysdatabases
WHERE [NAME] NOT IN ('MASTER', 'TEMPDB', 'MSDB', 'MODEL')
AND Status & 512 = 0
Regards,
Yenidai
Post #676283
jwalker8680
jwalker8680
Posted Thursday, April 23, 2009 10:46 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 26, 2013 12:28 PM
Points: 30,
Visits: 110
Excellent suggestions all... and all situations I never ran into
... Except maybe the length issue and I wasn't aware I was running into that issue...
Post #703423
JacekO
JacekO
Posted Thursday, April 23, 2009 10:50 AM
SSC-Addicted
Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473,
Visits: 606
jwalker8680 (4/23/2009)
Except maybe the length issue and I wasn't aware I was running into that issue...
Lucky you. I found out the hard way.
---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #703428
Congruent Influence
Congruent Influence
Posted Thursday, September 17, 2009 11:10 AM
Valued Member
Group: General Forum Members
Last Login: Friday, May 10, 2013 10:39 AM
Points: 70,
Visits: 135
Any suggestions on how to ignore results where the searched for text only occurs as part of a comment?
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
Post #789834
chandreshwar
chandreshwar
Posted Saturday, October 10, 2009 4:53 AM
Forum Newbie
Group: General Forum Members
Last Login: Saturday, October 10, 2009 4:46 AM
Points: 1,
Visits: 0
Hi,
There is an article posted on my Blog site where you can search for specific text in all sql server objects easily and the query is also very simple.
Search Text in all SQL Server Object
Cheers...
Post #801193
FairFunk
FairFunk
Posted 2 days ago @ 4:34 AM
SSC Rookie
Group: General Forum Members
Last Login: Today @ 6:40 AM
Points: 41,
Visits: 1,093
Red Gate SQL search will do this and much more as well as being totally free.
http://www.red-gate.com/products/sql-development/sql-search/
Post #1454914
Mark D Powell
Mark D Powell
Posted 2 days ago @ 12:41 PM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 12:26 PM
Points: 1,258,
Visits: 341
Josha, what is the main difference from this version and the version your wrote in July 2007 and updated in May 2009 that also performs a string search?
Post #1455166
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
