Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A stored procedure for searching a specific string in Sybase objects.
CREATE PROCEDURE dbo.TextSearch
(
@Text VARCHAR(100),
@Type CHAR(1) = NULL,
@Normalized BIT = 0
)
AS
BEGIN
IF (@Text IS NULL)
BEGIN
RAISERROR 1 'The search pattern must not be null.'
END
SET @Text = LTRIM(RTRIM(@Text))
IF (@Text = '')
BEGIN
RAISERROR 2 'The search pattern must not be empty.'
END
IF (CHARINDEX('%',@Text) > 0)
BEGIN
RAISERROR 3 'The search pattern must not contain wildcard characters.'
END
DECLARE @Pattern VARCHAR(100)
IF (@Normalized = 0)
BEGIN
SET @Pattern = '%' + @Text + '%'
SELECT DISTINCT
@Text AS 'Search',
O.name AS 'Match'
FROM
sysobjects O
INNER JOIN syscomments C1 ON C1.id = O.id
WHERE
((@Type = NULL) OR (O.type = @Type))
AND
(
C1.text LIKE @Pattern
OR
EXISTS
(
SELECT
*
FROM
syscomments C2
WHERE
C1.id = C2.id
AND (C1.colid + 1) = C2.colid
AND (RIGHT(C1.text,100) + LEFT(C2.text,100)) LIKE @Pattern
)
)
ORDER BY
'Match'
END
ELSE
BEGIN
SET @Pattern = '%' + LOWER(@Text) + '%'
SELECT DISTINCT
@Text AS 'Search',
O.name AS 'Match'
FROM
sysobjects O
JOIN syscomments C1 ON C1.id = O.id
WHERE
((@Type = NULL) OR (O.type = @Type))
AND
(
LOWER(C1.text) LIKE @Pattern
OR
EXISTS
(
SELECT
*
FROM
syscomments C2
WHERE
C1.id = C2.id
AND (C1.colid + 1) = C2.colid
AND LOWER(RIGHT(C1.text,100) + LEFT(C2.text,100)) LIKE @Pattern
)
)
ORDER BY
'Match'
END
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment