Searching Databases for a String

Use this query to search a string in the metadata of the database.

SET NOCOUNT ON
-- Set @searchstr to value that you want to search for in existing
-- procedures (all single quotes and % signs are required)
DECLARE @searchstr VARCHAR(50)
SELECT @searchstr = '''%1444%'''
SELECT @searchstr = RTRIM (@searchstr)
SELECT @searchstr AS Search_String

DECLARE @objname NVARCHAR(776)
DECLARE @dbname SYSNAME
DECLARE @blankspaceadded INT
DECLARE @basepos INT
DECLARE @currentpos INT
DECLARE @textlength INT
DECLARE @lineid INT
DECLARE @addonlen INT
DECLARE @lfcr INT
DECLARE @definedlength INT
DECLARE @syscomtext NVARCHAR(4000)
DECLARE @line NVARCHAR(255)

CREATE TABLE #storedproctext (
stored_proc_name VARCHAR(128), lineid
INTEGER,text NVARCHAR(255)
)

DECLARE sp_name_cursor SCROLL CURSOR
FOR SELECT name
FROM sysobjects (NOLOCK)
WHERE TYPE = 'P'
ORDER BY name
FOR READ ONLY

OPEN sp_name_cursor
FETCH NEXT FROM sp_name_cursor
INTO @objname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @definedlength = 255
SELECT @blankspaceadded = 0
SELECT @dbname = PARSENAME(@objname,3)
SELECT @lfcr = 2
SELECT @lineid = 1
DECLARE syscomcursor CURSOR
FOR SELECT text
FROM syscomments
WHERE id = OBJECT_ID(@objname)
AND encrypted = 0
ORDER BY number, colid
FOR READ ONLY
OPEN syscomcursor
FETCH NEXT FROM syscomcursor INTO @syscomtext
WHILE @@FETCH_STATUS >= 0
BEGIN
SELECT @basepos = 1
SELECT @currentpos = 1
SELECT @textlength = LEN(@syscomtext)
WHILE @currentpos != 0
BEGIN
SELECT @currentpos = CHARINDEX(CHAR(13)+CHAR(10),@syscomtext, @basepos)
IF @currentpos != 0
BEGIN
WHILE (ISNULL(LEN(@line),0) + @blankspaceadded + @currentpos-@basepos + @lfcr) > @definedlength
BEGIN
SELECT @addonlen = @definedlength-(ISNULL(LEN(@line),0) + @blankspaceadded)
INSERT #storedproctext VALUES ( @objname, @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 #storedproctext VALUES( @objname, @lineid,@line )
SELECT @lineid = @lineid + 1
SELECT @line = NULL
END
ELSE
BEGIN
IF @basepos < @textlength
BEGIN
WHILE (ISNULL(LEN(@line),0) + @blankspaceadded + @textlength-@basepos+1 ) > @definedlength
BEGIN
SELECT @addonlen = @definedlength - (ISNULL(LEN(@line),0) + @blankspaceadded )
INSERT #storedproctext VALUES
( @objname, @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 CHARINDEX(' ', @syscomtext, @textlength+1 ) > 0
BEGIN
SELECT @line = @line + ' ', @blankspaceadded =1
END
BREAK
END
END
END
FETCH NEXT FROM syscomcursor INTO @syscomtext
END
IF @line IS NOT NULL
INSERT #storedproctext VALUES( @objname, @lineid, @line )
END
CLOSE syscomcursor
DEALLOCATE syscomcursor
FETCH NEXT FROM sp_name_cursor
INTO @objname
END
CLOSE sp_name_cursor
DEALLOCATE sp_name_cursor

DECLARE @strSELECT NVARCHAR(300)

SET @strSELECT = 'SELECT DISTINCT (SUBSTRING(stored_proc_name,1,60)) AS Stored_Procedure_Name FROM #storedproctext WHERE text like ' +
@searchstr + 'ORDER BY Stored_Procedure_Name'

EXEC sp_executesql @strSELECT

DROP TABLE #storedproctext