SQL Server search for text in stored procedures and functions
SQL Server search for text in stored procedures and functions and get the line numbers of each row
About
A fairly simple bit of code, this can be used to pick up references and line numbers for certain key words within stored procedures and functions in your database.
It utilises the a function created in another article (link below), which splits strings into a table dependent on the input character of your choice. As we are passing in the return character to the function it means we can then drill down on what line the reference is made in.
SQL
DECLARE @SearchStr NVARCHAR(100)='Holidays'
SELECT OBJECT_NAME(object_id) ObjectName,object_id ObjectID,WordInt as Line,
REPLACE(REPLACE(REPLACE(REPLACE(WordStr,CHAR(13)+CHAR(10),''),CHAR(10),''),CHAR(13),''),CHAR(9),'') as LineText
FROM [utilities].sys.all_sql_modules
OUTER APPLY [utilities].dbo.TextToRows(CHAR(13),definition) ttr
WHERE definition like '%'+@SearchStr+'%' AND
WordStr like '%'+@SearchStr+'%'
SELECT OBJECT_NAME(object_id) ObjectName,object_id ObjectID,WordInt as Line,
REPLACE(REPLACE(REPLACE(REPLACE(WordStr,CHAR(13)+CHAR(10),''),CHAR(10),''),CHAR(13),''),CHAR(9),'') as LineText
FROM [utilities].sys.all_sql_modules
OUTER APPLY [utilities].dbo.TextToRows(CHAR(13),definition) ttr
WHERE definition like '%'+@SearchStr+'%' AND
WordStr like '%'+@SearchStr+'%'
This will bring back the following results from our database. They are three functions that do various calculations with dates excluding holidays and can be found in the SQL Functions section.
Results
| ObjectName | ObjectID | Line | LineText |
| CalcWorkDaysBetween | 1010102639 | 9 | IF (DATEPART(WEEKDAY,@Date)IN(1,7) OR (SELECT Count(*) FROM Holidays WHERE Date=@Date)=1) |
| CalcWorkDaysMonth | 1026102696 | 9 | IF (DATEPART(WEEKDAY,@Date) IN(1,7)OR(SELECT Count(*) FROM Holidays WHERE Date=@Date)=1) |
| CalcWorkDaysAddDays | 1042102753 | 10 | IF NOT (DATEPART(WEEKDAY,@Date) IN (1,7) OR EXISTS (SELECT * FROM Holidays WHERE Date=@Date)) |
