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


Search All SQL Script Objects in All Databases for Text


Search All SQL Script Objects in All Databases for Text

Author
Message
jwalker8680
jwalker8680
SSC-Addicted
SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)

Group: General Forum Members
Points: 482 Visits: 118
Comments posted to this topic are about the item Search All SQL Script Objects in All Databases for Text
JacekO
JacekO
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3925 Visits: 617
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.

shelleybobelly
shelleybobelly
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 785
Don't forget to clean up and drop the #Results table if running as a script not a procedure.
Yenidai
Yenidai
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 Visits: 134
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
jwalker8680
jwalker8680
SSC-Addicted
SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)

Group: General Forum Members
Points: 482 Visits: 118
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... :-P
JacekO
JacekO
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3925 Visits: 617
jwalker8680 (4/23/2009)
Except maybe the length issue and I wasn't aware I was running into that issue... :-P


Lucky you. I found out the hard way. :-D

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Congruent Influence
Congruent Influence
SSC Eights!
SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)

Group: General Forum Members
Points: 848 Visits: 154
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.
chandreshwar
chandreshwar
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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...
FairFunk
FairFunk
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 1696
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/
Mark D Powell
Mark D Powell
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3295 Visits: 496
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?
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