February 6, 2009 at 1:58 pm
Comments posted to this topic are about the item Search All SQL Script Objects in All Databases for Text
February 19, 2009 at 7:49 am
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.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 13, 2009 at 10:23 am
Don't forget to clean up and drop the #Results table if running as a script not a procedure.
March 16, 2009 at 12:58 am
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
April 23, 2009 at 10:46 am
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... 😛
April 23, 2009 at 10:50 am
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. 😀
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
September 17, 2009 at 11:10 am
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.
October 10, 2009 at 4:53 am
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...
May 21, 2013 at 4:34 am
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/
May 21, 2013 at 12:41 pm
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?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply