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 «««123

Searching Syscomments Accurately Expand / Collapse
Author
Message
Posted Thursday, January 25, 2007 5:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422, 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%'

Post #339668
Posted Thursday, January 25, 2007 12:11 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:20 AM
Points: 263, Visits: 275
Wow! That's much better! Nice work Jesper!


SQL guy and Houston Magician
Post #339878
Posted Thursday, January 25, 2007 12:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:20 AM
Points: 263, 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
Post #339881
Posted Monday, January 7, 2008 11:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 20, 2014 3:01 PM
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%'
Post #439728
Posted Friday, June 12, 2009 1:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 7, 2014 12:49 PM
Points: 3, Visits: 301
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 varcharAS Instance,

    
'...'+SUBSTRING(m.definitionCHARINDEX(@StringToFindm.definitionn.Number), 100)+'...' AS Snippet 

  
FROM

    
sys.sql_modules AS m

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

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

    
CROSS JOIN admin.tbl_Numbers AS n

  
WHERE 

   
n.Number LEN(m.definition

   AND 
SUBSTRING(m.definitionn.NumberLEN(@StringToFind)) @StringToFind

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

   

SET NOCOUNT OFF;

GO

Post #734103
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse