August 12, 2008 at 1:23 pm
I want to return multiple rows if there are multiple TODO entries in the SP text. I have tried a few recursive CTEs version of this working code which only returns the first instance and I can't get them to work.
DECLARE @Pattern AS varchar(6)
SET @Pattern = '%TODO%'
;WITH ROUTINES AS (
-- CANNOT use INFORMATION_SCHEMA.ROUTINES because of 4000 character limit
SELECT o.[name] AS ROUTINE_NAME, m.definition AS ROUTINE_DEFINITION
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.object_id = o.object_id
WHERE o.type_desc = 'SQL_STORED_PROCEDURE'
)
SELECT ROUTINE_NAME
,SUBSTRING(ROUTINE_DEFINITION, PATINDEX(@Pattern, ROUTINE_DEFINITION), 255) AS WorkItem
,PATINDEX(@Pattern, ROUTINE_DEFINITION) AS StartPos
FROM ROUTINES
WHERE ROUTINE_DEFINITION LIKE @Pattern
August 12, 2008 at 2:01 pm
This isn't nearly as tidy code as yours, but I use this code regularly and it works quite nicely:
[font="Courier New"]CREATE PROCEDURE #TSQL_Search (
@SearchString VARCHAR(40)
) AS
DECLARE
@SchemaName VARCHAR(128),
@ObjectName VARCHAR(128),
@ObjectType VARCHAR(128),
@Definition VARCHAR(max),
@CRLF CHAR(2),
@LineNum INT,
@LineDef VARCHAR(256),
@LineStartPos INT,
@LineEndPos INT,
@CharPos INT
BEGIN
SET NOCOUNT ON
SET @CRLF = CHAR(13) + CHAR(10)
DECLARE @ResultList TABLE (SchemaName VARCHAR(128), ObjectName VARCHAR(128), ObjectType VARCHAR(128), LineNum int, SQL VARCHAR(256))
DECLARE cur_Grep CURSOR FAST_FORWARD FOR
SELECT s.name AS schema_name, o.name AS object_name, o.type_desc, m.definition
FROM sys.objects o
INNER JOIN sys.sql_modules m ON o.object_id = m.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE m.definition like '%' + @SearchString + '%'
ORDER BY s.name, o.type_desc, o.name
OPEN cur_Grep
FETCH NEXT FROM cur_Grep INTO @SchemaName, @ObjectName, @ObjectType, @Definition
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LineNum = 1
SET @LineStartPos = 1
SET @LineEndPos = CHARINDEX(@CRLF, @Definition)
WHILE @LineStartPos > 0
BEGIN
IF @LineEndPos > 0
SET @LineDef = SUBSTRING(@Definition, @LineStartPos, @LineEndPos-@LineStartPos+1)
ELSE
SET @LineDef = SUBSTRING(@Definition, @LineStartPos, LEN(@Definition)-@LineStartPos+1)
SET @CharPos = CHARINDEX(@SearchString, @LineDef)
IF @CharPos > 0
INSERT INTO @ResultList VALUES (@SchemaName, @ObjectName, @ObjectType, @LineNum, @LineDef)
SET @LineStartPos = @LineEndPos
SET @LineEndPos = CHARINDEX(@CRLF, @Definition, @LineStartPos+2)
SET @LineNum = @LineNum + 1
END
FETCH NEXT FROM cur_Grep INTO @SchemaName, @ObjectName, @ObjectType, @Definition
END
CLOSE cur_Grep
DEALLOCATE cur_Grep
SELECT * FROM @ResultList
END
EXEC #TSQL_Search 'TODO'[/font]
August 12, 2008 at 2:06 pm
SELECTp.RoutineName,
'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec]
FROM(
SELECTOBJECT_NAME(so.ID) AS RoutineName,
(SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR XML PATH('')) AS Body
FROMSYSOBJECTS AS so
WHEREso.TYPE IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'V', 'X')
) AS p
WHEREp.Body LIKE '%print%'
N 56°04'39.16"
E 12°55'05.25"
August 12, 2008 at 4:27 pm
Finally got it working:
DECLARE @Pattern AS varchar(6)
SET @Pattern = '%TODO%'
;WITH ROUTINES AS (
-- CANNOT use INFORMATION_SCHEMA.ROUTINES because of 4000 character limit
SELECT o.[name] AS ROUTINE_NAME, m.definition AS ROUTINE_DEFINITION
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.object_id = o.object_id
WHERE o.type_desc = 'SQL_STORED_PROCEDURE'
AND m.definition LIKE @Pattern
)
,Results AS (
SELECT ROUTINE_NAME
,PATINDEX(@Pattern, ROUTINE_DEFINITION) AS StartPos
,SUBSTRING(ROUTINE_DEFINITION, PATINDEX(@Pattern, ROUTINE_DEFINITION), 255) AS WorkItem
,RIGHT(ROUTINE_DEFINITION, LEN(ROUTINE_DEFINITION) - PATINDEX(@Pattern, ROUTINE_DEFINITION)) AS Remainder
,1 AS Occurrence
FROM ROUTINES
UNION ALL
SELECT ROUTINE_NAME
,PATINDEX(@Pattern, Remainder) AS StartPos
,SUBSTRING(Remainder, PATINDEX(@Pattern, Remainder), 255) AS WorkItem
,RIGHT(Remainder, LEN(Remainder) - PATINDEX(@Pattern, Remainder)) AS Remainder
,Occurrence + 1 AS Occurrence
FROM Results
WHERE PATINDEX(@Pattern, Remainder) > 0
)
SELECT ROUTINE_NAME
,WorkItem
FROM Results
ORDER BY ROUTINE_NAME
,Occurrence
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply