SQL Server Source Code Search Utility. T-SQL utility to search SQL Server object definitions (procedures, views, functions, triggers) by text patterns, with inclusion/exclusion filters and contextual match snippets

sql_source_code_search_utility.sql

/********************************************************************************************
    SQL Server Source Code Search Utility
    -------------------------------------
    Searches SQL object definitions (procedures, views, functions, triggers)
    for specific text patterns, allowing inclusion and exclusion of both:
      • Text within object definitions
      • Object names

    Features:
      • Supports multiple comma-separated values for each category
      • Supports SQL wildcard characters (% and _)
      • Returns surrounding code context for each match.
      • Case-insensitive search
********************************************************************************************/


-- ==============================================
-- CONFIGURATION SECTION
-- ==============================================

-- Comma-separated lists of patterns. 
DECLARE
    -- text patterns to search for inside definitions
    @SearchTextList varchar(MAX) = 'dbo.',
    -- text patterns to exclude                                                                                                                                                                                                                                                                                                      
    @ExcludeTextList varchar(MAX) = 'CREATE PROCEDURE,dbo.dw_disabl,dbo.dw_enab,dbo.EMPTY,dbo.settings,dbo.c3_user_getTaskType',
    -- object name patterns to exclude (empty = exclude nothing) (Wildcards (% and _) are supported)
    @ExcludeObjectsList varchar(MAX) = 'rpt%,c3%,dw_switch,dw_reset,dw_setForceRun,FULL_GUID,dw_disableIndexes%,dw_enableIndexes%,dw_checkStats,dw_transform,dw_executeETL,dw_extract%,dw_getSettings%,dw_initialize%,dw_isDoNotRun%,dw_isForceRun%,dw_list%,dw_log%,dw_process%,dw_rebuildSynonyms',
    -- optional filters for object names (empty = include all) (Wildcards (% and _) are supported)
    @IncludeObjectsList varchar(MAX) = '',
    --optional filters by type of object. If empty, all object types will be included. (P=Stored Procedure, V=View, FN=Scalar Function, IF=Inline TVF, TF=Multi-Statement TVF, TR=Trigger, U=User Table.)
    @ObjectTypes nvarchar(MAX) = N'';


-- Control parameters
DECLARE @ShowAllMatches bit = 1, -- 1 = show all matches per object, 0 = show only the first
        @CharsAfter int = 200,   -- number of characters to display after match
        @CharsBefore int = 20;   -- number of characters to display before match
--SQL Prompt Formatting Off
-- Parameter tables generated from the lists above
DECLARE @SearchTexts TABLE (Pattern varchar(200) NOT NULL);
DECLARE @ExcludeTexts TABLE (Pattern varchar(200) NULL);
DECLARE @ExcludeObjects TABLE (Pattern varchar(200) NULL);
DECLARE @IncludeObjects TABLE (Pattern varchar(200) NULL);

-- Split lists into table rows, trimming spaces
INSERT INTO @SearchTexts (Pattern) SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@SearchTextList, ',') WHERE LTRIM(RTRIM(value)) <> '';
INSERT INTO @ExcludeTexts (Pattern) SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@ExcludeTextList, ',') WHERE LTRIM(RTRIM(value)) <> '';
INSERT INTO @ExcludeObjects (Pattern) SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@ExcludeObjectsList, ',') WHERE LTRIM(RTRIM(value)) <> '';
INSERT INTO @IncludeObjects (Pattern) SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@IncludeObjectsList, ',') WHERE LTRIM(RTRIM(value)) <> '';

--SQL Prompt Formatting On


-- ==============================================
-- PREPARATION
-- ==============================================

-- Temporary table to store results
IF OBJECT_ID('tempdb..#SearchResults') IS NOT NULL DROP TABLE #SearchResults;

CREATE TABLE #SearchResults
(
    Snippet nvarchar(MAX) NOT NULL,       -- code snippet containing the match
    ObjectName sysname NOT NULL,          -- object name
    SchemaName sysname NOT NULL,          -- schema name
    Position int NOT NULL,                -- position in definition
    ObjectType char(2) NOT NULL,          -- object type code (P, V, FN, etc.)
    ObjectTypeDesc nvarchar(60) NOT NULL, -- object type description
    CreatedAt datetime NOT NULL,          -- creation date
    ModifiedAt datetime NOT NULL          -- last modified date
);

-- Working variables
DECLARE @ObjectId int,
        @ObjectName sysname,
        @SchemaId int,
        @Definition nvarchar(MAX),
        @Position int,
        @SearchPattern varchar(200);


-- ==============================================
-- MAIN CURSOR: SELECT OBJECTS BASED ON FILTERS
-- ==============================================

DECLARE objectCursor CURSOR FOR
SELECT o.object_id,
       o.name,
       o.schema_id
FROM sys.objects o
    INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE
    -- excludes encrypted or system objects
    OBJECT_DEFINITION(o.object_id) IS NOT NULL
    AND (
            -- Include all types if @ObjectTypes is empty
            @ObjectTypes = ''
            OR
            -- Otherwise, filter by provided type codes
            EXISTS (
                       SELECT 1
                       FROM STRING_SPLIT(@ObjectTypes, ',') AS t
                       WHERE LTRIM(RTRIM(t.value)) = o.type
                   )
        )
    -- Include if definition matches at least one search pattern
    AND EXISTS (
                   SELECT 1
                   FROM @SearchTexts s
                   WHERE LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%' + LOWER(s.Pattern) + '%'
               )
    -- Include only if name matches at least one inclusion pattern (if provided)
    AND (
            NOT EXISTS (
                           SELECT 1 FROM @IncludeObjects
                       )
            OR EXISTS (
                          SELECT 1
                          FROM @IncludeObjects io
                          WHERE LOWER(o.name) LIKE LOWER(io.Pattern)
                      )
        )
    -- Exclude if name matches any exclusion pattern
    AND NOT EXISTS (
                       SELECT 1
                       FROM @ExcludeObjects eo
                       WHERE LOWER(o.name) LIKE LOWER(eo.Pattern)
                   );

OPEN objectCursor;
FETCH NEXT FROM objectCursor
INTO @ObjectId,
     @ObjectName,
     @SchemaId;


-- ==============================================
-- MAIN LOOP: SCAN OBJECT DEFINITIONS AND CAPTURE MATCHES
-- ==============================================
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Definition = OBJECT_DEFINITION(@ObjectId);

    -- Iterate over all text search patterns
    DECLARE searchCursor CURSOR FOR SELECT Pattern FROM @SearchTexts;
    OPEN searchCursor;
    FETCH NEXT FROM searchCursor
    INTO @SearchPattern;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Position = 1;

        WHILE @Position > 0
        BEGIN
            -- Find approximate match (ignore wildcard % for char search)
            SET @Position = CHARINDEX(REPLACE(@SearchPattern, '%', ''), @Definition, @Position);

            IF @Position > 0
            BEGIN
                -- Insert contextual snippet around match
                INSERT INTO #SearchResults
                (
                    Snippet,
                    ObjectName,
                    SchemaName,
                    Position,
                    ObjectType,
                    ObjectTypeDesc,
                    CreatedAt,
                    ModifiedAt
                )
                SELECT CASE
                           WHEN @Position - @CharsBefore > 1 THEN
                               '...'
                               + SUBSTRING(
                                              @Definition,
                                              @Position - @CharsBefore,
                                              @CharsBefore + LEN(REPLACE(@SearchPattern, '%', '')) + @CharsAfter
                                          )
                           ELSE
                               SUBSTRING(
                                            @Definition,
                                            1,
                                            @CharsBefore + LEN(REPLACE(@SearchPattern, '%', '')) + @CharsAfter
                                        )
                       END,
                       @ObjectName,
                       SCHEMA_NAME(@SchemaId),
                       @Position,
                       o.type,
                       o.type_desc,
                       o.create_date,
                       o.modify_date
                FROM sys.objects o
                WHERE o.object_id = @ObjectId;

                -- Move search position forward
                SET @Position = @Position + LEN(REPLACE(@SearchPattern, '%', ''));

                -- Stop early if only first match is required
                IF @ShowAllMatches = 0 BREAK;
            END;
        END;

        FETCH NEXT FROM searchCursor
        INTO @SearchPattern;
    END;

    CLOSE searchCursor;
    DEALLOCATE searchCursor;

    FETCH NEXT FROM objectCursor
    INTO @ObjectId,
         @ObjectName,
         @SchemaId;
END;

CLOSE objectCursor;
DEALLOCATE objectCursor;


-- ==============================================
-- OUTPUT RESULTS
-- ==============================================

SELECT Snippet,
       ObjectName,
       SchemaName,
       Position,
       ObjectType,
       ObjectTypeDesc,
       CreatedAt,
       ModifiedAt
FROM #SearchResults
WHERE NOT EXISTS (
                     SELECT 1
                     FROM @ExcludeTexts et
                     WHERE LOWER(Snippet) LIKE '%' + LOWER(et.Pattern) + '%'
                 )
ORDER BY ObjectName,
         Position;