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
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 118
Comments posted to this topic are about the item Search All SQL Script Objects in All Databases for Text
JacekO
JacekO
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: 2835 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
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 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 (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)

Group: General Forum Members
Points: 363 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
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 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
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: 2835 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
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 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
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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 Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 1655
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
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: 2771 Visits: 489
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