Get the #lines in a TSQL view/SP/UDF of a String

,

The following procedure gets a TSQL program object
(View/UDF/stored procedure) and a given string

It searched object's source code for the string and returns the number of lines where a match is found.

This is helpful when we want to search all our TSQL code for a specific table name, column etc. and investigate where it is referenced without using sp_depends function

use master 
go
Create Proc usp_find_num_line_occurences 
( @TSQLObject    varchar(80),
  @StringToFind  varchar(80),
  @wordCnt	  int OUTPUT)
as
begin
  set nocount on

  Create Table #tempSource (lineOfCode varchar(200))
  INSERT  #tempSource exec sp_helptext @TSQLObject

  select @wordCnt = count(*)
   from #tempSource
   where CharIndex (upper(@StringToFind), upper (lineOfCode),1) > 0

  set nocount off
end
go

-- Example of usage:
DECLARE @wordCnt int
exec usp_find_num_line_occurences 'sp_help',
				  'spt_values',
				  @wordCnt output
print @wordCnt

Rate

Share

Share

Rate