SQLServerCentral / Discuss Content Posted by Robert Cary / Article Discussions / Article Discussions by Author / Searching Syscomments Accurately / Latest Posts

RE: Searching Syscomments Accurately

Jason Hannas — Fri, 12 Jun 2009 13:22:38 GMT

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)ASSET 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

RE: Searching Syscomments Accurately

dtalsma — Mon, 07 Jan 2008 11:40:54 GMT

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%'

RE: Searching Syscomments Accurately

Robert Michael Cary — Thu, 25 Jan 2007 12:13:00 GMT

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.

RE: Searching Syscomments Accurately

Robert Michael Cary — Thu, 25 Jan 2007 12:11:00 GMT

Wow! That's much better! Nice work Jesper!

RE: Searching Syscomments Accurately

Jesper-244176 — Thu, 25 Jan 2007 05:40:00 GMT

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

SELECT distinct O.Name, O.TypeFROM( 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) CINNER JOIN sysobjects O ON C.id = O.IdWHERE C.TEXT LIKE '%yourSearchString%'

RE: Searching Syscomments Accurately

Robert Michael Cary — Tue, 23 Jan 2007 12:43:00 GMT

Sure! Specifically what code are you refering to? The code in the article or the code in the forum (or both)

RE: Searching Syscomments Accurately

Jags2001 — Tue, 23 Jan 2007 07:24:00 GMT

Hi,

In your code you use a lot of GROUP BY's --- can you explain why?

Thanks!

RE: Searching Syscomments Accurately

Ninja's_RGR'us — Tue, 23 Jan 2007 06:18:00 GMT

That will not work. This returns only the first row of text in syscomments (2000). No idea about 2005.

RE: Searching Syscomments Accurately

Michael Lysons — Tue, 23 Jan 2007 02:30:00 GMT

Yep, that's what I was going to do, just not got round to it yet

RE: Searching Syscomments Accurately

Jeremy Hoy-369361 — Tue, 23 Jan 2007 02:00:00 GMT

I think this also works.SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%some_text%' AND ROUTINE_TYPE='PROCEDURE'

RE: Searching Syscomments Accurately

Ramseier Andreas — Tue, 23 Jan 2007 00:09:00 GMT

This is also a good solution from Tim Chapman. He is using object_definition() function.http://www.sqlservercentral.com/articles/articlelink.asp?articleid=2809Andy

RE: Searching Syscomments Accurately

Robert Michael Cary — Mon, 22 Jan 2007 17:31:00 GMT

The 30 is an arbitary max of colid. If you have a proc greater than 120,000 bytes long, this will fail. Thank you for brining this up. I've thought about it and there are three options:1. Lookup the max colid value from syscomments before running the query (although this seems to kill the query plan)2. Use the maximum possible value (255) 3. Skip the numbers table and join syscomments to itself to generate your range of colId Values.Here is the code:

DECLARE @max SMALLINTSELECT @max = max(COlid) FROM syscomments SELECT DISTINCT O.Name, O.Type    FROM    (        SELECT Id,            CAST(COALESCE(MIN(CASE WHEN sc.colId = Num-1 THEN sc.text END), '') AS VARCHAR(8000)) +            CAST(COALESCE(MIN(CASE WHEN sc.colId = Num THEN sc.text END), '') AS VARCHAR(8000)) AS [text]        FROM syscomments SC            INNER JOIN numbers N                ON N.Num = SC.colid                OR N.num-1 = SC.colid        WHERE N.Num <= @max        GROUP BY id, Num    ) CINNER JOIN sysobjects O    ON C.id = O.IdWHERE C.TEXT LIKE '%yourSearchString%' SELECT DISTINCT O.Name, O.Type    FROM    (        SELECT Id,            CAST(COALESCE(MIN(CASE WHEN sc.colId = Num-1 THEN sc.text END), '') AS VARCHAR(8000)) +            CAST(COALESCE(MIN(CASE WHEN sc.colId = Num THEN sc.text END), '') AS VARCHAR(8000)) AS [text]        FROM syscomments SC            INNER JOIN numbers N                ON N.Num = SC.colid                OR N.num-1 = SC.colid        WHERE N.Num <= 255        GROUP BY id, Num    ) CINNER JOIN sysobjects O    ON C.id = O.IdWHERE C.TEXT LIKE '%yourSearchString%'SELECT DISTINCT O.Name, O.Type FROM ( SELECT Id,  CAST(COALESCE(MIN(CASE WHEN sc.colId = Num-1 THEN sc.text END), '') AS VARCHAR(8000)) +  CAST(COALESCE(MIN(CASE WHEN sc.colId = Num THEN sc.text END), '') AS VARCHAR(8000)) AS [text]FROM syscomments SC INNER JOIN (SELECT TOP 100 PERCENT ColID AS Num FROM Syscomments GROUP BY ColID ORDER BY ColID DESC) N ON N.Num = SC.colid OR N.num-1 = SC.colid GROUP BY id, Num) CINNER JOIN sysobjects O ON C.id = O.Id WHERE C.TEXT LIKE '%yourSearchString%'

Overall, the last query--with the self-join--appears to be the fastest; it has fewer scans but more reads.I hope this all helps

RE: Searching Syscomments Accurately

sql_er — Mon, 22 Jan 2007 14:58:00 GMT

Robert,

Your solution works great ... I am however having a bit of a problem following the code - could you clarify the significance of the number 30 used in the WHERE clause?

Thanks a lot

RE: Searching Syscomments Accurately

Robert Michael Cary — Mon, 22 Jan 2007 14:06:00 GMT

Thank you to everyone who has commented on the article. I've really enjoyed hearing how others have approached this problem and it's also great to hear that there are better approaches available in SQL2k. I've learned a lot from you guys!

RE: Searching Syscomments Accurately

Tim Chapman-218780 — Mon, 22 Jan 2007 14:01:00 GMT

Maybe...but its probably negligible. If you use sys.sql_modules, you still have to go lookup the name of the object.Tim

RE: Searching Syscomments Accurately

Rick Townsend — Mon, 22 Jan 2007 13:52:00 GMT

If you're using SQL Server 2005, wouldn't you do better to search sys.sql_modules? That's the replacement for syscomments. The definition column holds all the sql definitions, and is nvarchar(max).

I imagine the object_definition() function simply queries that view, so you should get better performance by searching the view directly rather than using the function.

RE: Searching Syscomments Accurately

Robert Michael Cary — Mon, 22 Jan 2007 09:57:00 GMT

That's definitly a valid approach, and I like that your solution give you line numbers. I have often wanted to expand out the functionality of my search app to be more like grep. It would be great to be able to see the line(s) the string appears on, so you have the context, line numbers etc.

RE: Searching Syscomments Accurately

Robert Michael Cary — Mon, 22 Jan 2007 09:54:00 GMT

OBJECT_DEFINITION sounds like a real winner! I hadn't heard of that function, it sounds perfect though!Thanks for sharing this tip Tim! I guess I need to swot up some more on SQL 2005!

RE: Searching Syscomments Accurately

Robert Michael Cary — Mon, 22 Jan 2007 09:53:00 GMT

If you are using an access front end, you can use the rich text box control to display the text and highlight the results. I implemented a similar thing years ago. If you really want it to fly, you can edit the raw RTF, but the performance is just as acceptable using the built in methods of the control.

RE: Searching Syscomments Accurately

Ronald Green-301498 — Mon, 22 Jan 2007 09:18:00 GMT

I usually know the whereabouts of the code I'm searching for, but if I really had to find something in the code, I'd probably script everything to a file and search for the string with a regular expressions enabled text editor since I would probably anyway have problems finding words with white space and/or line breaks between them.

RE: Searching Syscomments Accurately

Tim Chapman-218780 — Mon, 22 Jan 2007 06:51:00 GMT

I haven't tried it for this scenario, but I would think that you could call the new 2005 function OBJECT_DEFINITION on the sysobjects table, and then query that function to find the dependencies that you are looking for. OBJECT_DEFINITION returns a varchar(max), so it is searchable.SELECT name FROM sys.objectsWHERE OBJECT_DEFINITION(object_id) LIKE '%searchtext%'

RE: Searching Syscomments Accurately

Andy Warren — Mon, 22 Jan 2007 06:20:00 GMT

Nice work. I knew a proc could get split across multiple rows, just never ran into the scenario you describe where the word is split (or I ran into and didnt notice it!). Creating the super select as a view might come in handy.

RE: Searching Syscomments Accurately

Michael Lysons — Mon, 22 Jan 2007 03:36:00 GMT

Nice article

The solution I use is to populate a table overnight with the full syscomments text for each object, across multiple databases. This table can then be queried. We use an Access front-end for the querying, so we can return a list of matches and then drilldown for a further look into the full text.

An addition I'd like to make is to highlight the search string in the objects, like Google does with search results. I do like the idea of the line numbering mentioned above too.

RE: Searching Syscomments Accurately

Hugh Macmillan-281005 — Mon, 22 Jan 2007 03:20:00 GMT

I have a solution that I use to find occurrences of a string within any stored procedure in a database. Although is uses a cursor, I have found the performance to be more than acceptable. This solution also has the merit of telling you the line number that the match was found on.CREATE proc dbo.zspFind @Search varchar(100)asDECLARE cur CURSORREAD_ONLYFOR select name, syscomments.id, colid from syscomments join sysobjects on syscomments.id=sysobjects.id where xtype='P' and category=0 order by name, colidDECLARE @name varchar(100)DECLARE @id intDECLARE @colid intDECLARE @buf varchar(8000)DECLARE @pos1 intDECLARE @pos2 intDECLARE @line varchar(4000)DECLARE @lineno intdeclare @out table (Name varchar(100), line int, [text] varchar(1000)) OPEN curset @buf=''set @lineno=1FETCH NEXT FROM cur INTO @name, @id, @colidWHILE (@@fetch_status <> -1)BEGIN select @buf=@buf+cast([text] as varchar(4000)) from syscomments where id=@id and colid=@colid set @pos1=1 select @pos2=charindex(char(10), @buf, @pos1) while @pos2>0 begin select @line=substring(@buf, @pos1, @pos2-@pos1), @lineno=@lineno+1 if charindex(@Search, @line)>0 begin set @line =replace(@line, char(9), '') insert into @out values (@name, @lineno, @line) end select @pos1=@pos2+1 select @pos2=charindex(char(10), @buf, @pos1) end set @buf=substring(@buf, @pos1, 4000) FETCH NEXT FROM cur INTO @name, @id, @colid if @colid=1 set @lineNo=1ENDCLOSE curDEALLOCATE curselect * from @out

Searching Syscomments Accurately

Robert Michael Cary — Tue, 02 Jan 2007 11:55:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/rcary/2792.asp