IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo][StringSearch]’) AND TYPE IN (N’P’, N’PC’))
BEGIN
DROP PROCEDURE [dbo].[StringSearch]
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author : Doug Osborne
— Name : StringSearch
— Create Date : 03/02/2011
— Description : Search all text in syscomments for a string. Displays all matches, the
— 50 characters before and 50 characters after the match.
— The search term itself is surrounded by –[* *]–
— Modifications:
— =============================================
CREATE PROCEDURE [dbo].[StringSearch]
(
@Search VARCHAR(100) = ‘Program’
)
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED — WITH (NOWAIT)
BEGIN TRY
SELECT SO.Name AS [Name], SO2.Type_Desc AS [TYPE], REPLACE( SUBSTRING( SC.Text, CHARINDEX ( @Search, sc.Text ) – 100, 200 ), @Search, ‘–[*’ + UPPER( @Search ) + ‘*]–‘ ) AS [Snippet]
FROM syscomments AS SC
INNER JOIN sysobjects AS SO ON SC.ID = SO.ID
INNER JOIN sys.objects AS SO2 ON SO.Name = SO2.Name
WHERE sc.TEXT LIKE ‘%’ + @Search + ‘%’
END TRY
BEGIN CATCH
DECLARE
@ErrorNumber INT, — Returns the number of the error
@ErrorSeverity INT, — Returns the severity
@ErrorState INT, — Returns the error state number
@ErrorProcedure SYSNAME, — Returns the name of the stored procedure or trigger where the error occurred
@ErrorLine INT, — Returns the line number inside the routine that caused the error
@ErrorMessage NVARCHAR(2048), — Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times
@CRLF CHAR(2), — Carriage Return + Line Feed
@Msg VARCHAR(2000) — Standard SQL error message to log
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorProcedure = ISNULL( ERROR_PROCEDURE(), ‘inline code’ ),
@ErrorLine = ERROR_LINE(),
@ErrorMessage = ERROR_MESSAGE(),
@CRLF = CHAR(13) + CHAR(10)
SET @Msg = @CRLF +
‘Error [‘ + CAST( @ErrorNumber AS VARCHAR ) + ‘] Severity [‘ + CAST( @ErrorSeverity AS VARCHAR ) + ‘] State [‘ + CAST( @ErrorState AS VARCHAR ) + ‘].’ + @CRLF +
‘Procedure [‘ + @ErrorProcedure + ‘] Line [‘ + CAST( @ErrorLine AS VARCHAR ) + ‘].’ + @CRLF +
‘Message [‘ + @ErrorMessage + ‘]’
RAISERROR( ‘%s’, @ErrorSeverity, @ErrorState, @Msg ) WITH NOWAIT;
END CATCH
END
GO
sqlozzie 