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 Monday, January 22, 2007 2:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 12, 2009 7:37 AM
Points: 125, Visits: 115
Maybe...but its probably negligible. If you use sys.sql_modules, you still have to go lookup the name of the object.
Tim
Post #338799
Posted Monday, January 22, 2007 2:06 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
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!


SQL guy and Houston Magician
Post #338802
Posted Monday, January 22, 2007 2:58 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:17 PM
Points: 236, Visits: 474

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

Post #338809
Posted Monday, January 22, 2007 5:31 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
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 SMALLINT
SELECT @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
) C
INNER JOIN sysobjects O
ON C.id = O.Id
WHERE 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
) C
INNER JOIN sysobjects O
ON C.id = O.Id
WHERE 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
) C
INNER 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


SQL guy and Houston Magician
Post #338836
Posted Tuesday, January 23, 2007 12:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 7, 2008 11:26 PM
Points: 8, Visits: 90
This is also a good solution from Tim Chapman. He is using object_definition() function.

http://www.sqlservercentral.com/articles/articlelink.asp?articleid=2809

Andy
Post #338875
Posted Tuesday, January 23, 2007 2:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 21, 2014 1:58 AM
Points: 1, Visits: 8
I think this also works.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%some_text%'

AND ROUTINE_TYPE='PROCEDURE'
Post #338885
Posted Tuesday, January 23, 2007 2:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:19 AM
Points: 1,148, Visits: 1,071

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

 

Post #338890
Posted Tuesday, January 23, 2007 6:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:14 AM
Points: 21,385, Visits: 9,603
That will not work.  This returns only the first row of text in syscomments (2000).  No idea about 2005.
Post #338938
Posted Tuesday, January 23, 2007 7:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 21, 2012 7:39 AM
Points: 135, Visits: 128

Hi,

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

Thanks!

 




Post #338968
Posted Tuesday, January 23, 2007 12:43 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
Sure! Specifically what code are you refering to? The code in the article or the code in the forum (or both)


SQL guy and Houston Magician
Post #339122
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse