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


Searching Syscomments Accurately


Searching Syscomments Accurately

Author
Message
Jesper-244176
Jesper-244176
Say Hey Kid
Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)

Group: General Forum Members
Points: 704 Visits: 33

I modified your last query a bit and obtained something that seems to be faster on SQL Server 2000:

SELECT distinct O.Name, O.Type
FROM
(
select
s1.id,
cast(coalesce(s1.text, '') as varchar(8000)) +
cast(coalesce(s2.text, '') as varchar(8000))
as [text]
from syscomments s1 left join syscomments s2
on s2.id = s1.id and s2.colid = s1.colid + 1
) C
INNER JOIN sysobjects O ON C.id = O.Id
WHERE C.TEXT LIKE '%yourSearchString%'


R Michael
R Michael
SSC-Addicted
SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)

Group: General Forum Members
Points: 441 Visits: 275
Wow! That's much better! Nice work Jesper!

SQL guy and Houston Magician
R Michael
R Michael
SSC-Addicted
SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)

Group: General Forum Members
Points: 441 Visits: 275
It looks like some of those group bys are unneccessary. Check out Jesper's code for the best 2k approach to date. As far as an explanation goes, This code evolved quite a bit, I never reviewed the code as a whole prior to posting.
In fact, the 2k code was based on the same approach used in the 2k5 code.

SQL guy and Houston Magician
dtalsma
dtalsma
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 47
I recently was examining source code and used a different technique. I believe the following will accomplish the same thing in SQL 2005. I do not know if it works in SQL 2000.

SELECT OBJECT_NAME(object_ID) AS Name, definition
FROM sys.sql_modules
WHERE definition LIKE '%YourSearchString%'
Jason Hannas
Jason Hannas
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 364
I was searching for the 2005 replace for syscomments and found this thread. I see it's fairly old, but figured I'd post the solution I've just worked up this afternoon. It seems to be working out well for me--fast and accurate as far as I tell.

You will need the use of a Numbers/Tally table:


CREATE PROCEDURE admin.pFindStringInCode

@StringToFind nvarchar(50)

AS

SET
NOCOUNT ON;



SELECT

s.[name] AS SchemaName,

o.[name] AS ObjectName,

CAST(DENSE_RANK() OVER (ORDER BY s.[Name], o.[Name]) AS varchar) + '.' +

CAST(ROW_NUMBER() OVER (PARTITION BY s.[Name], o.[Name] ORDER BY s.[Name], o.[Name]) AS varchar) AS Instance,

'...'+SUBSTRING(m.definition, CHARINDEX(@StringToFind, m.definition, n.Number), 100)+'...' AS Snippet

FROM

sys.sql_modules AS m

JOIN sys.objects AS o ON o.[object_id] = m.[object_id]

JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]

CROSS JOIN admin.tbl_Numbers AS n

WHERE

n.Number < LEN(m.definition)

AND
SUBSTRING(m.definition, n.Number, LEN(@StringToFind)) = @StringToFind

AND m.definition LIKE N'%'+ @StringToFind + N'%'



SET NOCOUNT OFF;

GO

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