Here’s the SQL script I probably use most when I’m trying to work out dependencies and scope of development changes. Query Analyzer allows you to search objects (hit F4 – it’s cool!) and find objects by ‘name’, but there’s no obvious way to search stored procs for a particular string.
SQL Server hides Stored Procedure code in the sysComments table (in each database), and so it’s possible to search. This is another script I found from not sure where (so can’t really take credit). It does a great job of scanning and even gives you line numbers. It would be great to be able to just shove this into the master database, but you need to be in the context of the database you’re searching in (or it would have to be amended somewhat to run some more ‘dynamic’ SQL).
I have previously front-ended this for development databases with an ASP.NET page that simply runs the query substituting a parameter for the search text and connecting to the correct database. The results show nicely in a grid.
Here’s the code – just paste into a new query in your chosen database, change @SearchTerm and away you go…
SET NOCOUNT ON
GO
declare @dbname sysname
,@SearchTerm varchar(100)
,@BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int –lengths of line feed carriage return
,@DefinedLength int
,@obj_name varchar(255)
,@prev_obj_name varchar(255)
/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/
,@SyscomText nvarchar(4000)
,@Line nvarchar(255)
set @SearchTerm = ‘MyTable’
set @dbname = db_name()
set @SearchTerm = ‘%’ + @SearchTerm + ‘%’
Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores
trailing blank spaces*/
CREATE TABLE #CommentText
(obj_name varchar(255)
,LineId int
,Text nvarchar(255) collate database_default)
/*
** Make sure the @objname is local to the current database.
*/
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
end
begin
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT so.name, sc.text FROM syscomments sc
inner join sysobjects so
on so.id = sc.id
where so.type = ‘P’
AND sc.encrypted = 0
ORDER BY number, colid
FOR READ ONLY
end
/*
** Get the text.
*/
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
FETCH NEXT FROM ms_crs_syscom into @obj_name, @SyscomText
WHILE @@fetch_status >= 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)
IF @prev_obj_name <> @obj_name
–start again
SELECT @LineId = 1
WHILE @CurrentPos != 0
BEGIN
–Looking for end of line followed by carriage return
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)
–If carriage return found
IF @CurrentPos != 0
BEGIN
/*If new value for @Lines length will be > then the
**set length then insert current contents of @line
**and proceed.
*/
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @obj_name,
@LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N”)
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES( @obj_name, @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
–else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
/*If new value for @Lines length will be > then the
**defined length
*/
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength – (isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @obj_name,
@LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N”)
if LEN(@Line) < @DefinedLength and charindex(‘ ‘, @SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ‘ ‘, @BlankSpaceAdded = 1
END
END
END
END
–Save previous name
SET @prev_obj_name = @obj_name
FETCH NEXT FROM ms_crs_syscom into @obj_name, @SyscomText
END
IF @Line is NOT NULL
INSERT #CommentText VALUES( @obj_name, @LineId, @Line )
select * from #CommentText
where patindex(@SearchTerm, text) <> 0
CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
DROP TABLE #CommentText
Colorized by: CarlosAg.CodeColorizer
