A stored procedure for searching a specific string in Sybase objects.

TextSearch.sql

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