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

Search All SQL Script Objects in All Databases for Text Expand / Collapse
Author
Message
Posted Friday, February 6, 2009 1:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:57 AM
Points: 30, Visits: 111
Comments posted to this topic are about the item Search All SQL Script Objects in All Databases for Text
Post #651977
Posted Thursday, February 19, 2009 7:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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
Posted Friday, March 13, 2009 10:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 1:30 PM
Points: 18, Visits: 592
Don't forget to clean up and drop the #Results table if running as a script not a procedure.
Post #675385
Posted Monday, March 16, 2009 12:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 339, Visits: 106
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
Posted Thursday, April 23, 2009 10:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:57 AM
Points: 30, Visits: 111
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
Posted Thursday, April 23, 2009 10:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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
Posted Thursday, September 17, 2009 11:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:42 PM
Points: 70, Visits: 145
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
Posted Saturday, October 10, 2009 4:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, May 21, 2013 4:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 47, Visits: 1,340
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
Posted Tuesday, May 21, 2013 12:41 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 10:33 AM
Points: 1,358, Visits: 380
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 »

Add to briefcase

Permissions Expand / Collapse